Forum Discussion
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:
| 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 |
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 NewTableHope this helps
4 Replies
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 NewTablewith this result
I hope this is helpful
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 😅
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 NewTableHope this helps