Forum Discussion

Optrix's avatar
Optrix
New Member
5 years ago
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...
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    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"

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
5 years ago

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