Forum Discussion
Move multiple values from column which has multiple rows in a different column
Hi vermaaman,
This can also be done in Power Query. If you want it in DAX, create a new table as follows, where Table1 is the first table you show on the left:
NewTable = ADDCOLUMNS ( DISTINCT ( Table1[Col1] ), "Col2", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Col2] ), Table1[Col2], ";" ) ), "Col3", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Col3] ), Table1[Col3], ";" ) ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi vermaaman,
This can also be done in Power Query. If you want it in DAX, create a new table as follows, where Table1 is the first table you show on the left:
NewTable =
ADDCOLUMNS (
DISTINCT ( Table1[Col1] ),
"Col2", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Col2] ), Table1[Col2], ";" ) ),
"Col3", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Col3] ), Table1[Col3], ";" ) )
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks AlB for the swift response, can you plase advise if i have to write this formula in new measure?
Also if you have any link where i can transform the data using power query ?
That code is to create a new table. Nothing to do with a measure. If you want to do it in Power Query, paste the following M code in a blank query to see the steps. See it all at work in the attached file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcyxDcAgDETRXVzTMEICBEKygeX910i+XFzxiyedzt0OK1m1KMmTxEZipyTDQeJF4iRxUZLXm8RN4kPiSz/jAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]), #"Grouped Rows" = Table.Group(Source, {"Col1"}, {{"Col2", each [Col2]}, {"Col3", each [Col3]}}), #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Col2", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Col3", each Text.Combine(List.Transform(_, Text.From), ";"), type text}) in #"Extracted Values1"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers