Columns to Rows - Same column many times in row

[PatrickPerovan]

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:

Column1	Column2
Country	FR
Duration	0:7:54
Log_File	20230418_015218
Country	GB
Duration	0:5:59
Log_File	20230418_015203
Country	GB
Duration	0:7:54
Log_File	20230418_013410
Country	GB
Duration	0:0:3
Log_File	20230418_014015
Country	GB
Duration	0:7:54
Log_File	20230418_015218
Country	FR
Duration	0:6:15
Log_File	20230418_020644

I need to transform like that:

Country	Duration	Log_File
FR	0:7:54	20230418_015218
GB	0:5:59	20230418_015203
GB	0:7:54	20230418_013410
GB	0:0:3	20230418_014015
GB	0:7:54	20230418_015218
FR	0:6:15	20230418_020644

[PatrickPerovan]

Thanks!

[PatrickPerovan]

Hi @m_dekorte,

Many thanks! 😄

How to change reference using your code?

I posted an example here and when I tried to copy and paste using "Custom Column" it brings my example instead of my original table 😅

[m_dekorte · Accepted solution]

Hi @PatrickPerovan,

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]

Hi @PatrickPerovan,

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