Forum Discussion
Solved
Loading Table Column Types from Another Table
I've got two PowerQuery tables - one containing my actual data, one containing the data type for each row. For example, one table is... Name High Score Age Geoff 20 29 Sandra 203...
With non-primitive type support:
let // TextToType function #"Type Table" = Table.FromRows({ {"text", Text.Type}, {"Integer", Int64.Type}, {"Floating Point", Number.Type} }, Type.AddTableKey (type table [#"Json Type" = text, #"Actual Type" = type] , {"Json Type"}, true) ), TextToType = (jsontype as any) as type => #"Type Table"{[#"Json Type" = jsontype]}[Actual Type], // First table FirstTable = Table.FromRecords({ [name = "George", age = 22, score=50], [name = "Sarah", age = 19, score=201] }), // Second Table SecondTable = Table.FromRecords({ [name = "text", age = "Integer", score="Floating Point"] }), #"Add Types to Second Table" = Table.TransformColumns(Table.Transpose(Table.DemoteHeaders(SecondTable)), {{"Column2", TextToType, type type}}), TypeList = List.Zip ({#"Add Types to Second Table"[Column1], #"Add Types to Second Table"[Column2]}), // Transform Types #"Types to first table" = Table.TransformColumnTypes(FirstTable, TypeList) in #"Types to first table"So basically if you ever get more than these three types, you'll just need to add them in the #"Type Table"
Hi Optrix ,
Have a look at this approach:
let
// Type from text to actual
// You will need that hardcoded
TextTypes = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkmtKFHSUQoBUnohlQWpSrE60UqeeSWp6alFQHEgy8wEIeGWk59YkpmXrhCQn5kH0udXmpuUWgRVEAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"json name" = _t, #"type" = _t]),
TypeTable = Table.Distinct(Table.TransformColumns(TextTypes ,{{"type", (x) as type => Expression.Evaluate(x, #shared), type type}}), {"json name"}),
// First table
FirstTable = Table.FromRecords({
[name = "George", age = 22, score=50],
[name = "Sarah", age = 19, score=201]
}),
// Second Table
SecondTable = Table.FromRecords({
[name = "text", age = "Integer", score="Floating Point"]
}),
#"Add Types to Second Table" = Table.Join(Table.Transpose(Table.DemoteHeaders(SecondTable)), {"Column2"}, TypeTable, {"json name"}, JoinKind.Inner),
TypeList = List.Zip ({#"Add Types to Second Table"[Column1], #"Add Types to Second Table"[type]}),
// Transform Types
#"Types to first table" = Table.TransformColumnTypes(FirstTable, TypeList)
in
#"Types to first table"
Edit:
Sorry, seems they've changed something in the engine since I've last used #shared, now they do not allow it at all.
For now, you can fall back to primitive types:
...
TextTypes = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkmtKFHSUSqpLEhVALNjdaKVPPNKUtNTi2DieaW5SUAeSMYtJz+xJDMvXSEgPzOvBF1BLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"json name" = _t, #"type" = _t]),
TypeTable = Table.Distinct(Table.TransformColumns(TextTypes ,{{"type", (x) as type => Expression.Evaluate(x), type type}}), {"json name"}),
...With non-primitive type support:
let
// TextToType function
#"Type Table" = Table.FromRows({
{"text", Text.Type},
{"Integer", Int64.Type},
{"Floating Point", Number.Type}
},
Type.AddTableKey (type table [#"Json Type" = text, #"Actual Type" = type] , {"Json Type"}, true) ),
TextToType = (jsontype as any) as type => #"Type Table"{[#"Json Type" = jsontype]}[Actual Type],
// First table
FirstTable = Table.FromRecords({
[name = "George", age = 22, score=50],
[name = "Sarah", age = 19, score=201]
}),
// Second Table
SecondTable = Table.FromRecords({
[name = "text", age = "Integer", score="Floating Point"]
}),
#"Add Types to Second Table" = Table.TransformColumns(Table.Transpose(Table.DemoteHeaders(SecondTable)), {{"Column2", TextToType, type type}}),
TypeList = List.Zip ({#"Add Types to Second Table"[Column1], #"Add Types to Second Table"[Column2]}),
// Transform Types
#"Types to first table" = Table.TransformColumnTypes(FirstTable, TypeList)
in
#"Types to first table"So basically if you ever get more than these three types, you'll just need to add them in the #"Type Table"
