cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KJeffery
Frequent Visitor

Combining a column into one single cell

This is a wierd one and there is a peverse logic behind this as i am wanting to use the result in another query.  I am basically trying to get a whole column of data to combine into a single cell.  This must be done in power query (No DAX) and i do not want to have to mainly get these codes and mainly convert them on excel as this was my previous process and doing this several times a day is getting very time consuming.

 

Codes
4423135
2543456
453453
543453
453453

 

Desired Result

4423135, 2543456, 453453, 543453, 453453

 

I was trying to tranpose this data and the use a combine column function however the amount of data is dynamic so makes doing this extremely difficult.  Does anyone know a way to dynamically merge X amount of columns into a single column (preferably with a seperator but can figure this part out on my own)?

4 REPLIES 4
KJeffery
Frequent Visitor

Tried look through the examples given but no luck :(.  I did find a sort fo working way but this does not account for the changing amount of cost codes.  If the amount of codes were to change (highly likely) then the merge functionw ill break so if there is any suggesiton on how to make it just say merge all columns would be grateful.

 

Thanks for the help and example of my workings below

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYu5DcAwDAN3Ue3KfOzMInj/NSwoSCoeiWNmQKDijAwKk0326lwPIdJdZOgl19pQ/nf5r+WjaWLv8s8F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Code", Int64.Type}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}, "en-AU"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged")
in
#"Merged Columns"

Jakinta
Super User
Super User

 

let
    Source = Table.FromColumns({{1..10}}, {"Column1"} ),
    #"Added Custom" = Table.SelectColumns( Table.AddColumn(Source, "Custom", each Text.Combine( List.Transform(Source[Column1], Text.From) , ", ")), "Custom" )
in
    #"Added Custom"

 

KJeffery
Frequent Visitor

Thanks unfortunately this didn't work or i don't understand well enough.  I know the "1..10" is creating a list from 1 to 10 however when i use this with my list i only get a one line result.

watkinnc
Super User
Super User

Table.FromRows(List.Combine(Table.ToColumns(YourTableName)))

 

--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!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors