Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vermaaman
New Member

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

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.

Screenshot 2020-10-20 221524.jpg

 

Thank you

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

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 

SU18_powerbi_badge

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 ?

@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 

 

SU18_powerbi_badge

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.