Forum Discussion

LeviCaers's avatar
LeviCaers
Frequent Visitor
7 years ago
Solved

Nested column to new table

Hi,

I want to create a new table that combines two columns from different tables.
Table A
A
B
C

Table B
1
2
3

Result
A 1
A 2
A3
B 1
B 2
B 3
C 1
C 2
C3

Both values should be in different columns in the result table.

Anyone that knows how to do this?

Cheers

Need Help

2 Replies

  • alena2k's avatar
    alena2k
    Resolver IV
    7 years ago

    You can easily do it in Power Query: add second table as a custom column for the first one and expand it.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TableB),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"})
in
    #"Expanded Custom"