Loading Table Column Types from Another Table

[Optrix]

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	19
Jessica	23	32

While the other is...

Name	High Score	Age
Text	Floating Point	Integer

What I'd like to do is build an expression that sets the data type on table 1 based on the values in table 2 (in reality, both tables are loaded from the same JSON query - the types are in the first/header column of the returned data and split out using Table.First() and Table.RemoveFirstN())

I'm thinking I need to Table.Unpivot table #2 to get the names and values in a table, then perhaps a Table.TransformRows() in there to convert the names to types, followed by a Table.TransformColumnTypes to do the actual conversion - but the exact process is breaking my little brain.

let
   FirstTable = Table.FromRecords({
        [name = "George", age = 22, score=50],
        [name = "Sarah", age = 19, score=201],
    }),
   SecondTable = Table.FromRecords({
        [name = "text", age = "Integer", score="Floating Point"],
   }),
   Unpivoted = Table.UnPivot(SecondTable....) //This may be the key to the whole thing?
   Transformed = Table.TransformRows(UnPivoted,if [typename] = "Floating Point" then { [name], 
type number } else { [name], type text })
   Final = Table.TransformColumnTypes(FirstTable,Transformed)

I've got to admit, I'm struggling with M so far 😛

If anyone could help, it would be amazing.

[Jimmy801]

Hello @Optrix 

check out this approach

let
   FirstTable = Table.FromRecords({
        [name = "George", age = 22, score=50],
        [name = "Sarah", age = 19, score=201]
    }),
   SecondTable = List.Zip({Table.ColumnNames(Table.FromRecords({
        [name = type text, age = Int64.Type, score=type number]
   })), Record.FieldValues(Table.First(Table.FromRecords({
        [name = type text, age = Int64.Type, score=type number]
   })))}),

Transform = Table.TransformColumnTypes
(
     FirstTable,
     SecondTable
)


in 
    Transform

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[shaowu459]

Hi, @Optrix 

Please have this a try:

let
   FirstTable = Table.FromRecords({
        [name = "George", age = 22, score=50],
        [name = "Sarah", age = 19, score=201]
                                  }),
   SecondTable = Table.FromRecords({
        [name = "text", age = "Integer", score="Floating Point"]
                                  }),
                                  
   typeList = {{"Floating Point",Number.From},{"text", Text.From},{"Integer", Int64.From}},

   acc = List.Accumulate(
                         Table.ToColumns(Table.DemoteHeaders(SecondTable)),
                         FirstTable,
                         (x,y)=>
                               Table.TransformColumns(x,{y{0},List.Select(typeList,each _{0}=y{1}){0}{1}})
                        )
in
    acc

 

[Smauro]

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"}),
...

[Smauro · Accepted solution]

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"

[Optrix]

@Smauro - Ahh, that worked brilliantly!

This is the one I picked as my solution as it appeared to be the one with the clearest logic and greatest extensibility, but the support from everyone has been great.