Forum Discussion

NourJ's avatar
NourJ
Helper III
7 years ago
Solved

Combine text from multiple columns into one

Dear great cummunity,     i have Country1, Country2, Country3 and beside each column one column for the number of individual    i need to disply them in multi-row card as one value with...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi NourJ ,

     

    There are two solutions. Please download the demo from the attachment.

    1. Transform the data using M.

    Result = Table.Combine({Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col1", "Value1"}), {{"Col1", "Col"}, {"Value1", "Value"}}), 
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col2", "Value2"}), {{"Col2", "Col"}, {"Value2", "Value"}}), 
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col3", "Value3"}), {{"Col3", "Col"}, {"Value3", "Value"}})})

    2. Create a new table and establish three relationships.

    Cols =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table2[Col1] ),
            VALUES ( Table2[Col2] ),
            VALUES ( Table2[Col3] )
        )
    ),
    [Col1] <> BLANK ()
)

    Measure =
SUM ( Table2[Value1] )
    + CALCULATE (
        SUM ( Table2[Value2] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col2] )
    )
    + CALCULATE (
        SUM ( Table2[Value3] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col3] )
    )

    Combine-text-from-multiple-columns-into-one

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi NourJ ,

 

Can you post the expected result based on your snapshot, please? Would like the unique value in a column or unioning the three columns?

 

 

Best Regards,

NourJ's avatar
NourJ
Helper III
7 years ago

 

This is the result that i want to get, i want to have a column have the value of the three columns to usein slicer and in the multi-row card. this picture from old data, at that time i have it in one column while now i have to update my dashboard in the same visualization but the source of data have changed to three columns.

is it possible to have it like the attached pic?!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi NourJ ,

     

    There are two solutions. Please download the demo from the attachment.

    1. Transform the data using M.

    Result = Table.Combine({Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col1", "Value1"}), {{"Col1", "Col"}, {"Value1", "Value"}}), 
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col2", "Value2"}), {{"Col2", "Col"}, {"Value2", "Value"}}), 
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col3", "Value3"}), {{"Col3", "Col"}, {"Value3", "Value"}})})

    2. Create a new table and establish three relationships.

    Cols =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table2[Col1] ),
            VALUES ( Table2[Col2] ),
            VALUES ( Table2[Col3] )
        )
    ),
    [Col1] <> BLANK ()
)

    Measure =
SUM ( Table2[Value1] )
    + CALCULATE (
        SUM ( Table2[Value2] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col2] )
    )
    + CALCULATE (
        SUM ( Table2[Value3] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col3] )
    )

    Combine-text-from-multiple-columns-into-one

     

    Best Regards,

    • NourJ's avatar
      NourJ
      Helper III
      7 years ago

      v-jiascu-msftthank you so much it works with the second one