Forum Discussion

vermaaman's avatar
vermaaman
New Member
5 years ago
Solved

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.

 

Thank you

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    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 

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      vermaaman
      New Member
      5 years ago

      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's avatar
        AlB
        Community Champion
        5 years ago

        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