-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathQuery - FleetDataTable.html
More file actions
105 lines (88 loc) · 14.7 KB
/
Query - FleetDataTable.html
File metadata and controls
105 lines (88 loc) · 14.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Database>
<meta name=SourceType content=OLEDB>
<title>Query - FleetDataTable</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Description>Connection to the 'FleetDataTable' query in the workbook.</o:Description>
<o:Name>Query - FleetDataTable</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:PowerQueryConnection odc:Type="OLEDB">
<odc:ConnectionString>Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=FleetDataTable;Extended Properties=""</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText>SELECT * FROM [FleetDataTable]</odc:CommandText>
</odc:PowerQueryConnection>
<odc:PowerQueryMashupData><Mashup xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/DataMashup"><Client>EXCEL</Client><Version>2.105.342.0</Version><MinVersion>2.21.0.0</MinVersion><Culture>en-US</Culture><SafeCombine>false</SafeCombine><Items><Query Name="Folder to Import"><Description>The full path to the folder containing exported CSV battle reports.</Description><Formula><![CDATA["D:\Dev\STFC\battle reports\Query Source" meta [IsParameterQuery=true, List={"D:\Dev\STFC\battle reports\Query Source", "D:\Dev\STFC\battle reports\Ad Hoc"}, DefaultValue="D:\Dev\STFC\battle reports\Ad Hoc", Type="Text", IsParameterQueryRequired=true]]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query><Query Name="MergedTables"><Description>The converted blob. The "Data Model" will be used to format and parse the four tables from each exported report.</Description><Formula><![CDATA[let // Imports all CSV files as a binary blob into a list file. Source determined by "Folder to Import". Source = Folder.Files(#"Folder to Import"), // Do not import any hidden files. #"Filter Hidden Files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), // Convert the list of blobs into a single table. #"Invoke Transform Blob" = Table.AddColumn(#"Filter Hidden Files", "Transform File", each #"Transform Blob"([Content])), // Keep columns with file name and file contents, remove all other columns #"Trim Columns" = Table.SelectColumns(#"Invoke Transform Blob", {"Name", "Transform File"}), // Expand file contents into new columns #"Expand Columns" = Table.ExpandTableColumn(#"Trim Columns", "Transform File", Table.ColumnNames(#"Transform Blob"(Blob))), // New column that increments by 1 with each row. Used for table identification and grouping. #"Add Index Column" = Table.AddIndexColumn(#"Expand Columns", "Index", 0, 1, Int64.Type), // Same as Index Column but with a row break between tables. #"Add Adjusted Index Column" = Table.AddColumn(#"Add Index Column", "Adjusted", each if [Column1] <> "" then [Index] else null), // Identify header rows #"Add isHeader Column" = Table.AddColumn(#"Add Adjusted Index Column", "isHeader", each if [Column1] = "Player Name" then "Header" else if [Column1] = "Reward Name" then "Header" else if [Column1] = "Fleet Type" then "Header" else if [Column1] = "Round" then "Header" else null), // Places the appropriate TableName on each table header row #"Add TableName Column" = Table.AddColumn(#"Add isHeader Column", "TableName", each if [Column1] = "Player Name" then "PlayerData" else if [Column1] = "Reward Name" then "RewardData" else if [Column1] = "Fleet Type" then "FleetData" else if [Column1] = "Round" then "CombatData" else null), // Fill empty rows with appropriate table name (for grouping by table) #"Fill TableName Down" = Table.FillDown(#"Add TableName Column",{"TableName"}), // Copies contents of Name and places "SourceFile" in each table header row. #"Added SourceFile Column" = Table.AddColumn(#"Fill TableName Down", "SourceFile", each if [isHeader] <> null then "SourceFile" else [Name]), // Merge SourceFile and TableName with " || " as delimiter #"Generate Shortkey" = Table.AddColumn(#"Added SourceFile Column", "Short", each Text.Combine({[SourceFile], [TableName]}, " || "), type text), // A simple key column for lookups and merges #"Add ShortKey Column" = Table.AddColumn(#"Generate Shortkey", "ShortKey", each if [isHeader] = null then [Short] else "ShortKey"), #"Filtered Rows" = Table.SelectRows(#"Add ShortKey Column", each ([Adjusted] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Index", "Adjusted", "isHeader", "Short"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","--",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "TableName", "SourceFile", "ShortKey"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","NO",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "TableName", "SourceFile", "ShortKey"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","YES","1",Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "TableName", "SourceFile", "ShortKey"}), #"Grouped Rows" = Table.Group(#"Replaced Value2", {"TableName"}, {{"TableData", each _, type table [Column1=text, Column2=text, Column3=text, Column4=text, Column5=nullable text, Column6=nullable text, Column7=nullable text, Column8=nullable text, Column9=nullable text, Column10=text, Column11=nullable text, Column12=nullable text, Column13=nullable text, Column14=nullable text, Column15=nullable text, Column16=nullable text, Column17=nullable text, Column18=nullable text, Column19=nullable text, Column20=nullable text, Column21=nullable text, Column22=nullable text, Column23=nullable text, Column24=nullable text, Column25=text, Column26=text, Column27=text, Column28=text, Column29=text, Column30=text, TableName=text, SourceFile=text, ShortKey=text]}}) in #"Grouped Rows"]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query><Query Name="Transform Blob"><Formula><![CDATA[let Source = (#"Identify Blob Target" as binary) => let // Source is the blob file, determined by "Identify Blob Target" Source = Csv.Document(#"Identify Blob Target",[Delimiter=",", Columns=30, Encoding=65001, QuoteStyle=QuoteStyle.None]) in Source in Source]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query><Query Name="Blob"><Description>A list file containing all imported CSV files as binary blobs.</Description><Formula><![CDATA[let // Reads all CSV data in "Folder to Import" as a binary list. Source = Folder.Files(#"Folder to Import"), // Point to contents. Navigation1 = Source{0}[Content] in Navigation1]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query><Query Name="FleetDataTable"><Description>Third table in CSV. Contains ship info for all ships in attacking and defending fleet. 29 columns. One row per ship plus header.</Description><Formula><![CDATA[let Source = MergedTables, FleetData = Source{[TableName="FleetData"]}[TableData], #"Removed Duplicates" = Table.Distinct(FleetData), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Duplicates", [PromoteAllScalars=true]), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Fleet Type", "Attack", "Defense", "Health", "Ship Ability", "Captain Maneuver", "Officer One Ability", "Officer Two Ability", "Officer Three Ability", "Officer Attack Bonus", "Damage Per Round", "Armour Pierce", "Shield Pierce", "Accuracy", "Critical Chance", "Critical Damage", "Officer Defense Bonus", "Armour", "Shield Deflection", "Dodge", "Officer Health Bonus", "Shield Health", "Hull Health", "Impulse Speed", "Warp Range", "Warp Speed", "Cargo Capacity", "Protected Cargo", "Mining Bonus", "FleetData", "SourceFile", "ShortKey"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"FleetData", "TableType"}}) in #"Renamed Columns"]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query></Items></Mashup></odc:PowerQueryMashupData>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
�
</head>
<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
</td>
<td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>
<script language='javascript'>
function init() {
var sName, sDescription;
var i, j;
try {
sName = unescape(location.href)
i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }
i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }
document.title = sName;
document.getElementById("tdName").innerText = sName;
sDescription = document.getElementById("docprops").innerHTML;
i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);
if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {
}
}
</script>
</body>
�
</html>