Forum Discussion

KJeffery's avatar
KJeffery
Frequent Visitor
5 years ago

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.  T...
Need Help
KJeffery's avatar
KJeffery
Frequent Visitor
5 years ago

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"