How to insert rows in a table and pivot it

[Syndicate_Admin]

Hello everybody,

I'm still living at 100% my journey with PowerQuery and I'm currently facing an issue that could be solved with your suuport.

I have a table the below (Init) and I would like to transform it as below (2nd Step)

Init:

Col 1 Col 2 Col3 Col4

  A       1        C     3

  B       2        D     4

           |

          \/

1st Step:

Col 1 Col 2

  A       1

  B       2

  C       3

  D      4

      |

     \/

2nd Step:

A B C D

1 2  3 4

First, I wanted to obtain the data from Col3 and Col4 by using Record.FromList([Col4],[Col3])

Then, use Table.InsertRow to add the data from Col3 and Col4 under the data within Col1 and Col2. But it doesn't work. I guess, I'm not properly using the function Table.InsertRow.

To conclude, I wanted to use the funciton Table.Pivot, to get my final table wish.

Maybe, there is another way to succeed but for now, I can't find it, that's why I'm requiring your support.

Thanks in advance for your help.

Rémi

[dufoq3]

Same result, but perhaps easier to understand:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmcgNlaK1YlWcgKyjIDYBYhNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    toCols = Table.ToColumns(Source),
    EvenPositions = List.Select(List.Positions(toCols), each Number.IsEven(_)),
    OddPositions = List.Select(List.Positions(toCols), each Number.IsOdd(_)),
    ListOfTexts = List.Combine(List.Transform(EvenPositions, each toCols{_})),
    ListOfNumbers = List.Combine(List.Transform(OddPositions, each toCols{_})),
    #"1st_Step" = Table.FromColumns({ListOfTexts} & {ListOfNumbers}, {"Column1", "Column2"}),
    #"2nd_Step" = Table.Transpose(#"1st_Step"),
    #"2nd_StepMerged" = Table.CombineColumns(#"2nd_Step", Table.ColumnNames(#"2nd_Step"), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"2nd_StepMerged"

[Syndicate_Admin]

Hello,

Thanks for your answer. It works perfectly even if I do not clearly understand how it works 😉

Rémi

[AlienSx · Accepted solution]

Hello, @RVIG there is another way

let
    Source = init_table,
    cols = List.Buffer(Table.ToColumns(Source)),
    even = List.Combine(List.Alternate(cols, 1, 1, 0)),
    odd = List.Combine(List.Alternate(cols, 1, 1, 1)),
    tbl = Table.FromRows({odd, even})
in
    tbl