Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Columns to Rows - Same column many times in row

Hi all, I tried to figure out how to adjust this table. I tried transpose, unpivot, pivot, group by but I kind of stuck here. Could someone help with that?   That is the table that I have it:  ...
Need Help
Show and Tell
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi Anonymous,

     

    Instead of a custom column, copy the sample script.

    Add a new blank query, open up the advanced editor - delete the let-expression in there and paste the code sample in.

     

    Next replace the expression assigned to the Source step by a reference to the query with your production data. To illustrate if that query had the name "My Query" then it should look like below.

     

     

    let
    Source = #"My Query",
    SplitTable = Table.Split( Source, 3),
    NewTable = Table.FromRecords( List.Transform( SplitTable, each Record.FromList( _[Column2], _[Column1])))
in
    NewTable

     

     

    Hope this helps

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi Anonymous,

 

You could give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlTSUXILUorViVZyKS1KLMnMzwOKGFiZW5magEV98tPj3TJzUoGiRgZGxgYmhhbxBoamRoYWYGmEKe5OmKaYWpla4jXFwJgIUwi4xdjE0IAIUwysjPEYYgJ0DeVOwRIs2ALXzApqGVZTjAzMTICWxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    SplitTable = Table.Split( Source, 3),
    NewTable = Table.FromRecords( List.Transform( SplitTable, each Record.FromList( _[Column2], _[Column1])))
in
    NewTable

 

with this result

I hope this is helpful