Converting rows of data into one row

[mitchpj75]

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

[Anonymous]

Or this:

= Table.FromRows({List.Transform(List.Combine({Table.ToColumns(YourTableName)}), each Text.Combine(_, ";"))}, Table.ColumnNames(Table1))

--Nate

[tackytechtom · Accepted solution]

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/

[mitchpj75]

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.

[tackytechtom]

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/

[AlienSx]

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
    a