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
Hi All,
I am hoping i could get some help,
I have two set of queries on Power Query and for query 1 ( I have duplicates data due to entries on different date), i have sorted the date in descending order cause i would like to keep the latest date, i removed duplicates (using customer number ) after sorting and then proceed to merge query 1 with query 2 using a uniques value (customer number). To my amusement , the merge query is returning values in query 1 that was meant to have removed through duplicates down
So say Customer A has Unique number 5 and has dates pn the 31/10/2024 and 21/10/2024 when i remove the duplicates , i could only see 31/10/2024 values (which is fine) but upon merging with query 2 , i am seeing values of 21/10/2024 been returned
How can i resolve this please
Thanks
Solved! Go to Solution.
on the steps, after sorting data, a new step will added into your query steps which its formula starts with Table.Sort(....), befor removing the duplicteas, revise this formula by addin Table.Buffer befor it and rewrite the formula as
Table.Buffer(Table.Sort(....))
then remove duplicat. your peoblem become solved
Hi @olumidef ,
I tried to reproduce the whole step and it works properly in my case.
Could you check “Query1” again to make sure that the last result it returns does not contain the date 21/10/2024?
Here's my test file.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
on the steps, after sorting data, a new step will added into your query steps which its formula starts with Table.Sort(....), befor removing the duplicteas, revise this formula by addin Table.Buffer befor it and rewrite the formula as
Table.Buffer(Table.Sort(....))
then remove duplicat. your peoblem become solved
You are welcome
Hi @Omid_Motamedise , I see you introduce the Table.Buffer in the solution. Can you explain why that works? In my mind it is only a performance function and does nothing functionally.
Hi, it is true that Table.Buffer is mostely for performance but in this case, it can be used. Power query do not run the step, after each other in the order you create and it try to find the best order. so consider the below two steps
S0=Source,
S1=Table.Sort(S0,....),
S2=Table.Distinct(S1,....)
in thiss case, it seems that S1 is neglected and the duplicate rows are removed befor sorting, adding Table.Buffer in this step, forced power query to sort the rows and remove duplicate.
this problem can be solved in another way by combining the S1 and S2 and rewrite it as
S0=Source,
Srev=Table.Distinct(Table.Sort(S0,....),....)
Ok. But wouldn't that be a bug in powerquery? Your 2 examples should be equivalent. In both cases S0 should be evaluated first because S1 depends on it...
It might be but not in all the case, consider this example
S0=Source,
S1= Table.Addcolumn(S0,"x",....),
S2=Table.Group(S1,"A", {"m",Table.RowCount})
it is true that s2 is calculated based on S1, but its result does not dependent to the result of S1, if you use S0 also in this function, the result would be the same (adding a new column, does not changed the result of counting the rows)
Sorry, can't help you then...
PLease share your actual Copy/Pasted M code. The code you shared has syntax errors and non-existing colum names.... So not possible to debug.
The process is an applied steps on power query and those are the code relating to the steps in questions
Due to data privacy issue , unable to share the data/references
Not certain what the syntax error might be also
hi @olumidef ,
I understand data privacy issues.
If you could provide a sample input and output, masking sensitive information in a usable format ( tables, excel, csv, gdrive/ onedrive etc. link to pbix ) , would be happy to support.
for reference:
Please share your M code and examples of your data.
I am unable to provide the sample data due to privacy issue
However, i have created a replica as shown below for QUERY 1
Ac No | Customer Name | PIN | SD | ED | Created |
1 | A | 0001 | 31/10/2024 | 27/02/2027 | 31/10/2024 |
2 | B | 0002 | 07/11/2024 | 06/11/2027 | 31/10/2024 |
3 | C | 0003 | 30/10/2024 | 31/12/9998 | 31/10/2024 |
4 | D | 0004 | 01/01/2025 | 31/12/2025 | 31/10/2024 |
4 | D | 0004 | 01/09/2024 | 31/12/9998 | 21/10/2024 |
5 | E | 0005 | 02/07/2025 | 01/07/2028 | 31/10/2024 |
5 | E | 0005 | 08/08/2024 | 22/09/2024 | 22/10/2024 |
6 | F | 0006 | 01/10/2024 | 31/12/9998 | 31/10/2024 |
6 | F | 0006 | 14/08/2024 | 30/10/2024 | 26/10/2024 |
The data is first sorted by created to show newest to oldest
and duplucate is removed for which it returns the below
Ac No | Customer Name | PIN | SD | ED | Created |
1 | A | 0001 | 31/10/2024 | 27/02/2027 | 31/10/2024 |
2 | B | 0002 | 07/11/2024 | 06/11/2027 | 31/10/2024 |
3 | C | 0003 | 30/10/2024 | 31/12/9998 | 31/10/2024 |
4 | D | 0004 | 01/01/2025 | 31/12/2025 | 31/10/2024 |
5 | E | 0005 | 02/07/2025 | 01/07/2028 | 31/10/2024 |
6 | F | 0006 | 01/10/2024 | 31/12/9998 | 31/10/2024 |
This is then meant to be merge with another query 2 using the Pin, but ended up returning the deleted values after merging
The Steps code :
Sorting : = Table.Sort(#"Changed Type1",{{"Created", Order.Descending}})
Removing Duplicate: = Table.Distinct(#"Sorted Rows1", {"PIN"})
Merging on query 2: = Table.NestedJoin(#"Added Custom", {"PIN1 "}, #"Query1", {"PIN"}, "Query1", JoinKind.LeftOuter)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
16 | |
16 | |
11 |