Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone,
I have two working tables (A & B) and the ideal transformed result table (C) below in the attached Excel/PBI format.
Ideally I would create a relationship (it would need to be many to many) between table A & B to allow the two datasets to be used in the same visualisation/have filters apply etc. Wildcards cannot be used within the PK/FK in a relationship which means all possible permutations of the wildcards would need to be generated prior to the relationship being generated.
I’m unsure how to generate these wildcard permutations in an efficient manner as there are 2k rows, many rows have multiple wildcard and each wildcard could be A-Z or 1-9 so 35 possible permutations per wildcard which means there are likely multiple millions of wildcard permutations when fully extrapolated.
If there is a simpler and/or easier solution then I would greatly appreciate it. I'm open to any suggestions on how to get around the issue.
Sample Data:
Thanks
Solved! Go to Solution.
just delete the second condition of the FILTER function's second parameter.
like "&&len()=len()
Hi @campbellmurphy,
A possible PQ solution:
1) Create a new table, to go through the actual codes generated with the wildcards. This is going to be used as an intermediary to link TABLE A and TABLE B. One note, as seen with your data, in case of overlap both KPIs are linked (ABCD will get linked to AB?D and A??D)
2) Add a column in TABLE B, to get the max Length of the code based on TABLE A:
3) Create the relationships of #"TABLE A: KPIs"[#"Code (PK)"]<>>#"newtable"[#"Code (PK)"] and #"TABLE B: Balances"[#"Code"]<<>#"newtable"[Code"]
Hi @campbellmurphy,
A possible PQ solution:
1) Create a new table, to go through the actual codes generated with the wildcards. This is going to be used as an intermediary to link TABLE A and TABLE B. One note, as seen with your data, in case of overlap both KPIs are linked (ABCD will get linked to AB?D and A??D)
2) Add a column in TABLE B, to get the max Length of the code based on TABLE A:
3) Create the relationships of #"TABLE A: KPIs"[#"Code (PK)"]<>>#"newtable"[#"Code (PK)"] and #"TABLE B: Balances"[#"Code"]<<>#"newtable"[Code"]
Thanks Smauro this was also a good solution. In many ways it works even better. I really appreciate you taking the time
try this code to generate a new table
Hi @wdx223_Daniel,
I really appreciate the code you’ve sent through. When I brought it back into my actual dataset I found an additional complication which I hadn't accounted for (see below).
The Code (FK) in Table B often has additional alphanumeric values at the end of the string relative to the values in Code (PK) so the length won’t necessarily match. I’ve attempted to resolve this issue by using the CONTAINSSTRING function instead of LEN but haven’t had success. Any suggestions would be greatly appreciated.
Sample Data (Updated):
Transformed Data Excel
Transformed Data Power BI
Thanks
just delete the second condition of the FILTER function's second parameter.
like "&&len()=len()
@campbellmurphy Should be able to use GENERATE
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.