Last active
July 13, 2020 01:20
-
-
Save ninmonkey/34fdeeb76930ee22168a1468006c9bb8 to your computer and use it in GitHub Desktop.
Generating tables PowerQuery
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
/* summarize list of numbers or anything as a csv-like value | |
Example: | |
SummarizeList({0..3}) | |
Output: | |
{0, 1, 2, 3} | |
Example: | |
SummarizeList( {"a", 34, 234.5, DateTime.LocalNow() } ) | |
Output: | |
{ a, 34, 234.5, 7/12/2020 7:45:00 PM } | |
*/ | |
SummarizeList = (source as list) as text => | |
let | |
TextList = List.Transform( | |
source, | |
(element) => Text.From( element ) | |
), | |
String = Text.Combine(TextList, ", "), | |
Result = "{ " & String & " }" | |
in | |
Result | |
in | |
SummarizeList |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
/* converts existing table to Json | |
May require extra cleanup depending on datatype compatibility with Json | |
Usage: | |
TableToJson( TableName ) | |
*/ | |
TableToJson = (source as table, optional encoding as nullable number) as text => | |
let | |
encoding = if encoding <> null then encoding else TextEncoding.Utf8, | |
bin = Json.FromValue(source, encoding), | |
jsonAsText = Text.FromBinary(bin, encoding) | |
in | |
jsonAsText | |
in | |
TableToJson |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
// generate a configurable amount of datetimes | |
Config = [ | |
DateTimeStart = #datetime(2020, 1, 1, 0, 0, 0), | |
DateTimeStep = #duration(0, 7, 47, 59), | |
StepCount = 200 | |
], | |
listDatetimes = List.DateTimes( | |
Config[DateTimeStart], Config[StepCount], Config[DateTimeStep] | |
) | |
in | |
listDatetimes |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
// manually convert a table to Json | |
// see: `func TableToJson.pq` | |
Source = SourceTable, | |
bin = Json.FromValue( | |
Source, | |
TextEncoding.Utf8 | |
), | |
#"CopyPasteable Json" = Text.FromBinary( | |
bin, | |
TextEncoding.Utf8 | |
) | |
in | |
#"CopyPasteable Json" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
Source = #"gen DateTimes", | |
TableDates = Table.FromList( | |
Source, | |
Splitter.SplitByNothing(), | |
{"SaleDate"}, | |
null, | |
ExtraValues.Error | |
), | |
// warning: Random tables in PowerQuery require index or | |
// other operations that force evaluation | |
// otherwise lazy-evaluation ends up with the same value | |
// preview mode can appear correct until you hit apply | |
#"Random Sales" = Table.AddColumn( | |
TableDates, | |
"Value", | |
(row) => Number.RandomBetween(-40, 23), | |
Currency.Type | |
), | |
AddIndex = Table.AddIndexColumn( | |
#"Random Sales", "SaleId", 0, 1 | |
) | |
in | |
AddIndex |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[ | |
{ | |
"State": "California", | |
"Population": 39512223 | |
}, | |
{ | |
"State": "Hawaii", | |
"Population": 1415872 | |
}, | |
{ | |
"State": "Wyoming", | |
"Population": 578759 | |
} | |
] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
PathJson = "C:\Users\username\Documents\Power BI\Table Data.json", | |
SourceJson = Json.Document(File.Contents(PathJson)), | |
Source = Table.FromList( | |
SourceJson, | |
Splitter.SplitByNothing(), | |
null, null, ExtraValues.Error | |
), | |
FinalTable = Table.ExpandRecordColumn( | |
Source, | |
"Column1", | |
{"State", "Population"}, | |
{"State", "Population"} | |
) | |
in | |
FinalTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
RecordList = { | |
[State = "California", Population = 39512223 ], | |
[State = "Hawaii", Population = 1415872 ], | |
[State = "Wyoming", Population = 578759 ] | |
}, | |
Table = Table.FromRecords( | |
RecordList, | |
{"State", "Population"} | |
), | |
Source = Table.TransformColumnTypes( | |
Table, | |
{ | |
{"Population", Int64.Type} | |
} | |
) | |
in | |
Source |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
/* | |
param1: column names | |
param2: a list of lists (values) | |
*/ | |
source = #table( | |
{ "Name", "Id"}, | |
{ | |
{ "North", 1 }, | |
{ "West", 2 }, | |
{ "South", 1 } | |
} | |
) | |
in | |
source |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
/* | |
Start with a generated list, then transform it | |
output: | |
{ 0, 1, 2, 3, 4, 5, 6, 7 } | |
{ 1, 2, 4, 8, 16, 32, 64, 128 } | |
*/ | |
NumberList = {0..7}, | |
PowersOfTwo = List.Transform( | |
NumberList, | |
(power) => Number.Power(2, power) | |
), | |
Results = [ | |
Numbers = SummarizeList( NumberList ), | |
PowersOfTwo = SummarizeList( PowersOfTwo ) | |
] | |
in | |
Results |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment