Merging not behaving correctly

[MagikJukas]

Hello,

I am going crazy with this one. I really struggle to understand what is wrong.

I am merging two queries.

As you can see, Data Type is "1-Stock R." and Date = 28/6/2023.

When I expand the table, something completly different shows up:

Data Type is "5-Production" and Date "1/7/2023"

Furthermore the sales document is also different ?!?

any idea what is causing this behavior?

Join Kind is Left Outer.

thanks

[MagikJukas · Accepted solution]

Hello to @AlienSx  and @Anonymous 

so, after some testing I realized there is probably a bug in Powerquery in a previous step.

The Index column you see in the table was generate previously following these instruction:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

then when merging the two tables together based on the Key (which is depending on the Index) it gave me the bug I reported.

It seems powerquery recalculates the index based on some kind of logic of its own. In fact, before running the index I sorted the data in a specific order. when the merge occurs however, the Index ranking somehow ignores the previous sorting step. this is why we see different numbers.

Solution:

I figured out a solution.

Basically, I sorted the data, then created an index before groupping the data.

When I then groupped the data, I calculated for each set of table the first index value.

Then, I expanded the column contanining the tables and I substracted from the original index value the "Min Index". I now have a Index by category and it does not give me the problem mentioned above.

some details of the solution adopted:

https://community.fabric.microsoft.com/t5/Power-Query/Max-value-from-each-table/m-p/3307976#M106786

regards

[Anonymous]

Hi @MagikJukas ,

When expanded, the line in front of you also changes. Could you please tell me what your data source connection pattern is? What is a data source?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[AlienSx]

Hello, @MagikJukas Have you tried to refresh preview?