Move multiple values from column which has multiple rows in a different column

[vermaaman]

Hello, 

I just started Power BI yesterday, i am new.

I want to move the data from one column which has distinct values in rows but the other column has same values in rows as shown in the pitcure. 

Would be really helpful if someone can advise please.

Thank you

[AlB · Accepted solution]

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 

[vermaaman]

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 ?

[AlB]

@vermaaman 

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