Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have one table, which includes transactional level information.
The Columns are:
1) Customer Name (i.e. Client A, B)
2) Category (i.e. Dog, Cat)
3) Project Name (Alpha, Beta)
4) Location (i.e. China, UK)
5) Many other useless columns relating to other categories)
6) Financial Amount
7) A merged unique ID from Column (1, 2, 3, 4) (i.e. Client A-Dog-Alpha-UK)
I want to generate a table which would generate a list of all possible combinations of the first 4 columns so the following table would have columns like:
Client A | Dog | Alpha | China
Client B | Dog | Alpha | China
Client A | Cat | Alpha | China
Client B | Cat | Alpha | China
Client A | Dog | Beta | China
Client B | Dog | Beta | China
Client A | Cat | Beta | China
Client B | Cat | Beta | China
and so on.
I was advised to use the Crossjoin function. Any advice?
Solved! Go to Solution.
Hi @Anonymous ,
Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Anonymous ,
Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Its okay. I worked out a workaround. I just concentated all the columns together into a new calculated column in the initial table, then used a new calcualted table with the values( function to create a list of unique IDs.
I have succeeded in generating a table as follows:
Sorry just something to add on.
There is no need for ALL POSSIBLE combinations. just combinations that exist in the transactional data (i.e. If there is no financials from Client A for Dog, then I wouldn't need that combination in my new table
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |