March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Expecting output
1. In the power query, split the first column.
2. Create two duplicates of the table.
3. Delete the first splitted column in the first table and delete the second splitted column in the second table.
4. Append as a new quesry , the duplicates of the table by renaming as same column name.
5. Delete the duplicates.
6. Make the new relaationship between the appended table and the table2.
7. filter the blank values in the id.
Try the following :
FILTER(
Table2,
SEARCH(
MAX(Table1[sysid]),
Table2[sysid],
,
0
) > 0
)
The MAX function will get the maximum value of the sysid column in Table1, assuming that each sysid appears only once in Table1.
The SEARCH function will search for the sysid text in the sysid column of Table2. If the sysid text is found, the function returns value different from 0, which is used to filter Table2 using the FILTER function.
Be careful with the SEARCH function since it is case-sensitive, so if you want to perform a case-insensitive search, using FIND can be an alternative.
Thanks for the reply,
but, i am getting error like this when I tried
I tried to reproduce what you provided in the screenshot. You need to create a calculated table like below :
I am attaching the PBI file.
You are trying to create a calculated column while in the solution I provided it is a calculated table
Can you provide your expected output ?
Same error for calculated table also
expected output:
I am attaching the PBI file.
Based on the PBI file you provided :
Table 1 :
Table 2 :
the output will only contain data for the row with sysid=KLS in the table 2 and the status will be DONE.
The other rows will have status null as there is no matching between sysid from Table1 and syside from Table2 for the values HGF, KNF, JKO, IOK
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |