Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
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"
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"
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.
Table.FromRows(List.Combine(Table.ToColumns(YourTableName)))
--Nate
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.