Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to generate combination of values found in row data.
Source Data
Customer | Column2 |
Paul | A |
Paul | B |
Paul | C |
Tom | D |
Tom | E |
Tom | F |
Jerry | H |
Jerry | I |
Jerry | J |
Expected Output
Customer | Combined Column |
Paul | A-B |
Paul | A-C |
Paul | B-C |
Tom | D-E |
Tom | D-F |
Tom | E-F |
Jerry | H-I |
Jerry | H-J |
Jerry | I-J |
Any suggestion on how this can be achieved either in M or DAX?
Even if the combinations are repeated, for example A-B and B-A, I am fine with that.
Thanks in advance for your help.
Anand.
Solved! Go to Solution.
This seems close, albeit with duplicates:
Table = VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2]) VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2]) VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2])) VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2]) RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])
Hi,
See the image. Download file from here.
Thanks to your solution I made a minor adjustment to step3. Removed "[__Column2]>[___Column2]||". And that gave the exact result I nmeeded.
VAR __tmpTable3 =
FILTER (
GENERATE ( __tmpTable1, __tmpTable2 ),
[__Customer] = [___Customer]
&& ([__Column2] < [___Column2] )
)
Hi,
Here's the M code i used
let
Source = Table.NestedJoin(Data,{"Customer"},#"Data (2)",{"Customer"},"Data (2)",JoinKind.LeftOuter),
#"Expanded Data (2)" = Table.ExpandTableColumn(Source, "Data (2)", {"Column2"}, {"Column2.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data (2)", "Custom", each [Column2]&"-"&[Column2.1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column2.1"})
in
#"Removed Columns"
I downloaded the data into a table named Data.
Then from Get Source I selected Blank Query and type your M code.
But it does not create a table. I get the following:
Hi,
See the image. Download file from here.
That is a very simple solution using M.
Thanks a lot.
It needs some tweaking to remove same letter combination, e.g. A-A, but still very easy to achieve.
Thank you for your help.
You are welcome.
This seems close, albeit with duplicates:
Table = VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2]) VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2]) VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2])) VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2]) RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])
Thanks to your solution I made a minor adjustment to step3. Removed "[__Column2]>[___Column2]||". And that gave the exact result I nmeeded.
VAR __tmpTable3 =
FILTER (
GENERATE ( __tmpTable1, __tmpTable2 ),
[__Customer] = [___Customer]
&& ([__Column2] < [___Column2] )
)
That's works! Excellent solution!
Thanks a lot for the prompt reply.
I can sort out the duplicate combination.
Thanks you.
Not sure if this helps you or if i understand what you are attempting to achieve becuase the Combine Column values seem random.
If am wrong and they are not random i apologise.
For a given Customer Paul join the first letter of their name with the value in Column2
Combination = [Column2] &"-"& LEFT( [Customer],1 )
The result will be: A-P
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |