-
Notifications
You must be signed in to change notification settings - Fork 0
/
Query - data.odc
105 lines (88 loc) · 6.88 KB
/
Query - data.odc
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 - data</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 'data' query in the workbook.</o:Description>
<o:Name>Query - data</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=data;Extended Properties=""</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText>SELECT * FROM [data]</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.78.5740.481</Version><MinVersion>2.21.0.0</MinVersion><Culture>en-DE</Culture><SafeCombine>true</SafeCombine><Items><Query Name="data"><Formula><![CDATA[let Source = Csv.Document(File.Contents("C:\REPOS\MasterScraper\data.csv"),[Delimiter=",", Columns=17, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"applyBy", type date}, {"startDate", type date}}), #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "uniRank", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"uniRank.1", "uniRank.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"uniRank.2"}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"applyBy", #date(1999, 9, 9)}}), #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"startDate", #date(1999, 9, 9)}}), #"Reordered Columns" = Table.ReorderColumns(#"Replaced Errors1",{"platformLink", "country", "city", "uni", "title", "degType", "campus", "duration", "tuition", "about", "uniRank.1", "languages", "ects", "origLink", "applyBy", "startDate", "disciplines"}), #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",",","",Replacer.ReplaceText,{"tuition"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Free","0",Replacer.ReplaceText,{"tuition"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"tuition", Int64.Type}}), #"Replaced Errors2" = Table.ReplaceErrorValues(#"Changed Type1", {{"tuition", -999}}), #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Errors2",{{"uniRank.1", Int64.Type}}), #"Replaced Errors3" = Table.ReplaceErrorValues(#"Changed Type2", {{"uniRank.1", 99999}}), #"Reordered Columns1" = Table.ReorderColumns(#"Replaced Errors3",{"platformLink", "country", "city", "uni", "title", "uniRank.1", "tuition", "disciplines", "startDate", "applyBy", "degType", "campus", "duration", "languages", "ects", "about", "origLink"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns1",{{"uniRank.1", Order.Ascending}}) in #"Sorted Rows"]]></Formula><IsParameterQuery xsi:nil="true" /><IsDirectQuery xsi:nil="true" /></Query></Items></Mashup></odc:PowerQueryMashupData>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>