Forum Discussion
Converting rows of data into one row
Hi all,
I have the CURRENT output format as follows. What I need to do is get the data formatted into the DESIRED format. I have no idea how to do that - any advice?
| CURRENT | |||
| case_id | case_row_id | Date of Call | Call successful? |
| 922528 | 0 | 01/03/2023 | No |
| 922528 | 1 | 01/03/2023 | Yes |
| DESIRED | |||
| case_id | case_row_id | Date of call | Call successful? |
| 922528;922528 | 0;1 | 01/03/2023;01/03/2023 | No;Yes |
Hi mitchpj75 ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQyMjWyUNJRMgBhQ30DY30jAyNjIMcvXylWB0mBIbqCyNRipdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [case_id = _t, case_row_id = _t, #"Date of Call" = _t, #"Call successful?" = _t]), #"Renamed Columns" = Table.RenameColumns(Source,{{"Call successful?", "Call successful"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"case_id"}, {{"case_row_id", each Text.Combine([case_row_id], ";"), type nullable text}, {"Date of Call", each Text.Combine([Date of Call], ";"), type nullable text}, {"Call successful?", each Text.Combine([Call successful], ";"), type nullable text}}) in #"Grouped Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
5 Replies
Hi, mitchpj75
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQyMjWyUNJRMgBhQ30DY30jAyNjIMcvXylWB0mBIbqCyNRipdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [case_id = _t, case_row_id = _t, #"Date of Call" = _t, #"Call successful?" = _t]), a = Table.FromRows({List.Transform(Table.ToColumns(Source), each Text.Combine(_, ";"))}, Table.ColumnNames(Source)) in aHi mitchpj75 ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQyMjWyUNJRMgBhQ30DY30jAyNjIMcvXylWB0mBIbqCyNRipdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [case_id = _t, case_row_id = _t, #"Date of Call" = _t, #"Call successful?" = _t]), #"Renamed Columns" = Table.RenameColumns(Source,{{"Call successful?", "Call successful"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"case_id"}, {{"case_row_id", each Text.Combine([case_row_id], ";"), type nullable text}, {"Date of Call", each Text.Combine([Date of Call], ";"), type nullable text}, {"Call successful?", each Text.Combine([Call successful], ";"), type nullable text}}) in #"Grouped Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks. That is the output that I need.
Where do I paste the code in? Below is the table and the applied steps thus far to get me to my current position.
Hi mitchpj75 ,
Here a screenshot where you can see how to get to the advanced editor where you can paste the M code:
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
