-
-
Save gustavoleo/46395b86df37249e34b1884b0962c34f to your computer and use it in GitHub Desktop.
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 fnCreateDimTables = | |
(t as table, optional exclusions as list) => | |
let | |
// Get the schema for the table | |
Schema = Table.Schema(t), | |
// Select the text columns | |
AllTextCols = Table.SelectRows(Schema, each _[Kind] = "text")[Name], | |
// If there are columns to exclude, then exclude them | |
TextCols = if exclusions <> null then List.RemoveItems(AllTextCols, exclusions) else AllTextCols, | |
// Figure out the max index to assist with List.Generate() | |
MaxIndex = List.Count(TextCols) - 1, | |
// Build the list which figures out the unique values and builds a nested table | |
cols = | |
let | |
tbl = t | |
in | |
List.Generate( | |
// Generate a record | |
() => [ | |
// first field is the index | |
i = 0, | |
// second field is the table with an index column and the dimension value, sorted in ascending order | |
tbl = Table.AddIndexColumn( | |
Table.Sort( | |
Table.Distinct(Table.SelectColumns(tbl, TextCols{0}), Comparer.Ordinal), | |
{{TextCols{0}, Order.Ascending}} | |
), | |
// Set the index to the column name with _ID appended at the end | |
TextCols{0} & "_id", | |
1, | |
1 | |
), | |
// Third field is the name of the column | |
dimension = TextCols{0} | |
], | |
// Generate while the incrementer is less than or equal to the max index | |
each [i] <= MaxIndex, | |
// create the next records | |
each [ | |
i = [i] + 1, | |
tbl = Table.AddIndexColumn( | |
Table.Sort( | |
Table.Distinct(Table.SelectColumns(tbl, TextCols{[i] + 1}), Comparer.Ordinal), | |
{{TextCols{[i] + 1}, Order.Ascending}} | |
), | |
TextCols{[i] + 1} & "_id", | |
1, | |
1 | |
), | |
dimension = TextCols{[i] + 1} | |
] | |
), | |
// Convert the list of records to table | |
ToTable = Table.FromList(cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error), | |
// Expand the record column | |
Expanded = Table.ExpandRecordColumn( | |
ToTable, | |
"Column1", | |
{"i", "tbl", "dimension"}, | |
{"i", "tbl", "dimension"} | |
), | |
// Set the data types | |
ChangedTypes = Table.TransformColumnTypes( | |
Expanded, | |
{{"i", Int64.Type}, {"dimension", type text}} | |
) | |
in | |
ChangedTypes, | |
// Documentation | |
fnType = type function (t as table, optional exclusions as list) as table | |
meta [ | |
Documentation.Name = "fnCreateDimTables", | |
Documentation.LongDescription | |
= "This function takes a table and creates DimTables for all text columns, minus an optional list of columns you choose to exclude." | |
] | |
in | |
Value.ReplaceType(fnCreateDimTables, fnType) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment