Value Missing in expanded table from a merged query

Hi All,

I merged two queries in Power query, the first table is the output of a merge of two other queries.

My challenge is that after merging i am able to see all the values, by clicking on the table link, but once i expand the table, some rows have values missing for some of the expanded columns.

I have used the solution in the attached link to resolve the problem, but I would want to understand why it happened.
The attached link describes the exact problem that I had. Has anyone experienced similar issue with Power Query, where you able to find out what caused it.
Link to Similar Issue

@Obi Join type must be incorrect.

@AntrikshSharma,

That was my initial thinking but for my case, but I can see some of the data pulled for a row but some other are missing. If it was the join, I should not be getting any data for the row. Besides, when I use Table.ExpandTableColumn(Table.Buffer), I am able to see the missing data.

I am using a left outer join.

Hi @Obi,
try to add an index to test if your problem is as you described or perhaps a simple resorting with the expansion. With the added index it is easy for you to check if you are looking at the same data row before (preview by clicking on the right side of Table) and after the expansion.

The remedy you tried is Table.Buffer which helps you to keep the sort order, but doesn’t really change the data. You have not more data because of this. Just in the same row as in the step before.

It is quite normal to have empty rows with a left outer join if there is no match.

Regards,
Matthias

Thanks all. I have found out what the problem was, the connecting query had duplicate rows.

Hi @Obi,

great that you solved the underlying problem! :+1:

I am still curious though about that you described “once i expand the table, some rows have values missing for some of the expanded columns.” You applied Table.Buffer as solution and wanted to understand why it happened.

I explained the behaviour you described (why you saw “different” data after expansion) plus the effect of Table.Buffer (no change of the data, just preserving the sorting). Therefore it’s fine that you solved the underlying data issue, but it would also be interesting to learn if my assessment was correct.

Thanks,
Matthias