Forum Discussion
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 without duplication
is there a way to do so?
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] ) )
Best Regards,
6 Replies
UNION the VALUES of your columns together and then do a DISTINCT perhaps.
Greg_Decklerhow i can union them, i forgot to mention that this data is being updated mothly. so how can i union them ? is there a formula to do it or i had to add extra table manualy?
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,
