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.  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)?

Need Help

4 Replies

  • 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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

     

    --Nate

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

     

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

      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.