keep type information when transforming tables to records

[calon]

Hello Power BI Community! After having spent a few hours on this (mainly because I can't let it go), I registered just to ask this question:

I have a query in which I perform a Nested Join to add some additional Data. Each of these nested tables will contain only 1 Row, which I would like to transform to records. The problem is, that I seem to lose all type information when I do this. Is there a way to transform a line of a table with specified types and retain these types? Alternatively, is there a way to generate a custom Record type from a table's Column types?

Keeps types:

let
    Data = Table.FromColumns({{1..5},{"a".."e"}}, type table[Col1=number, Col2=text]),
    Source = Table.FromColumns({{1..5}}, type table[Col1=number]),
    Merge = Table.NestedJoin(Source, {"Col1"}, Data, {"Col1"}, "Data", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Data", {"Col1", "Col2"}, {"Data.Col1", "Data.Col2"})
in
    Expand

Types need to be reassigned:

let
    Data = Table.FromColumns({{1..5},{"a".."e"}}, type table[Col1=number, Col2=text]),
    Source = Table.FromColumns({{1..5}}, type table[Col1=number]),
    Merge = Table.NestedJoin(Source, {"Col1"}, Data, {"Col1"}, "Data", JoinKind.LeftOuter),
    TakeRecord = Table.TransformColumns(Merge,{{"Data",each _{0}, type [Col1=number,Col2=text]}}),
    Expand = Table.ExpandRecordColumn(TakeRecord, "Data", {"Col1", "Col2"}, {"Data.Col1", "Data.Col2"})
in
    Expand

I am well aware that I do not HAVE to do it this way. I am however very interested in learning more about the Power Query type system and I thought this problem would deliver some insight for me. I was also wondering if there is any speed difference in processing tables vs records.

Thanks in advance!

[mahoneypat]

You should be able to do the following but I haven't confirmed:

1. In a separate query, use Table.Schema(TableToBeMerged) to get the Names and Types for all the columns prior to the merge

2.  In that same query, use List.Zip on the Name and Type columns to make a list of lists of the Name/Type pairs.  For example - List.Zip({Source[Name], Source[Type]})

3. After your merge, use the List of Lists from 2 in a Table.TransformColumnTypes to get the original column types back.

Regards,

Pat

[calon]

Thanks, I had played around with Table.Schema and I can imagine many uses for it - but how do I make a custom record type with the list of lists? A record type looks like this as far as I know:

type [Col1=number,Col2=text]

I just don't know how I would create that from a list, since without the "type" before it, PQ doesn't even accept something like "Col1=number" in a list, right?

[v-yingjl]

Hi @calon ,

As far as I know, list functions in power query have no parameters about data type, so a direct way to change the data type when transforming to record after merging tables is like your mentioned.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.