Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mitchpj75
Helper I
Helper I

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_idcase_row_idDate of CallCall successful?
922528001/03/2023No
922528101/03/2023Yes
    
DESIRED   
case_idcase_row_idDate of callCall successful?
922528;9225280;101/03/2023;01/03/2023No;Yes
1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @mitchpj75 ,

 

How about this:

tackytechtom_0-1680722271396.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
watkinnc
Super User
Super User

Or this:

 

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

 

watkinnc_0-1680800099935.png

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
tackytechtom
Super User
Super User

Hi @mitchpj75 ,

 

How about this:

tackytechtom_0-1680722271396.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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.

 

mitchpj75_1-1680723266398.png

 

 

Hi @mitchpj75 ,

 

Here a screenshot where you can see how to get to the advanced editor where you can paste the M code:

tackytechtom_0-1680754566334.png

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

AlienSx
Super User
Super User

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

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors