cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdusek92 Helper II
Helper II

Power Query - Row disappear after expand column

Hello,

I have encountered a strange thing when I expand merged table column.

I use JoinKind.LeftOuter to merge queries - that should keep all the rows from the first/left table - and it does.

But when I expand the column I loose 2 out of 719 rows. How is that possible?

 

Here is a gif with only 2 filtered rows, but it shows the behavior:

 

 

expand2.gif

 

the table contains personal details so I cannot share it or show more columns but it is always the SAME PERSON that disappears after expanding.

 

EDIT:

I wanted to add a custom column showing the row count of the expading table, but after this step, the row DOES NOT dissapear:

 

expand3.gif

 

 

 

Any explanation please? I really dont understand that and it really scares me that I might be loosing rows in my other queries!

(the same thing happens in Power Query Excel)

 

Warm regards

Jakub

 

 

12 REPLIES 12
Community Support
Community Support

Re: Power Query - Row disappear after expand column

hi, @jdusek92 

I have tested on my side, but not reproduce the issue.

I think there should be something wrong with your JoinKind.LeftOuter to merge queries,

Could you please check if the matching column from two tables could be mached.

If not your case, please use virtual data or some sample data to create a simple sample pbix file for us have a test.

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jdusek92 Helper II
Helper II

Re: Power Query - Row disappear after expand column

Hello,

unfortunately I cannot share sample data - the source data is pulled from ACCESS db.

when I try to store sample data in Excel table, the problem does not occur.

 

Clearing cache did not help

 

Anyways I  tried to use Table.Buffer in Expand step:

Please see the gif below that show different results with/without Table.Buffer:

 

expand4.gif

 

Could anyone please explain different results?

Does it mean that the basic merge/join feature is that unreliable and I have to go through all my queries (dozens) and check for lost rows?

 

Warm regards

Jakub

Community Support
Community Support

Re: Power Query - Row disappear after expand column

hi, @jdusek92 

From the screenshot, there is no match value in"KATALOG" table, so that leads to this issue.

eg. there is no "999" in idPos column of "KATALOG" table. Therefore it returns null value.

https://docs.microsoft.com/en-us/powerquery-m/table-join

https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftouter

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jdusek92 Helper II
Helper II

Re: Power Query - Row disappear after expand column

Hello,

for anyone wondering - it is a bug and I have provided MS developers with a sample file:

https://drive.google.com/file/d/1SC0o3eO_HftVtJ-imU0bUc6XHj_b0dsW/view?usp=sharing

 

expand5.gif

 

 

After few hours I was able to prepare a sample excel file that carries the source data, queries and also the BUG.

 

query “FINAL” contains the BUG in the last step.

 

I have also discovered a new fact:

  • Expand Column step does not only remove some rows. But also the resulting rows are completely different – it returns back rows that have been removed in previous steps. (see the attached gif)
  • As I was able to place everything in Excel file that means that it has nothing to do with the Access connector
  • My guess is that the combination of sorted rows and remove duplicates is to blame
  • If I add Table.Buffer in the Expand Column it works fine
    • = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1"}, {"Query1.Column1"})
    • = Table.ExpandTableColumn(Table.Buffer(#"Merged Queries"), "Query1", {"Column1"}, {"Query1.Column1"})
  • If I add a custom column BEFORE Expand Query:
    • = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([Query1]))  - DOES HELP
    • = Table.AddColumn(#"Merged Queries", "Custom", each "SSSS") DOES NOT HELP

 

Looking at the things that help, it seems to me that the query needs to be “kicked” to “recalculate” with functions like Table.Buffer or Table.RowCount that forces the query to do it.

 

 

Community Support
Community Support

Re: Power Query - Row disappear after expand column

hi, @jdusek92 

Thank you for your feedback, I have reported this to power bi product team, if there is any update, I will post here.

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ccampagna
New Member

Re: Power Query - Row disappear after expand column

Hi to everyone!

 

Im just reporting that the issue still exist. Thanks to @jdusek92 for the table.buffer solution it helped me a lot.

 

Best Regards,

 

Campagna Cristian

Operational Data analyst on Aerolineas Argentinas

Veles Advocate IV
Advocate IV

Re: Power Query - Row disappear after expand column

Thanks for this, unfortunately the Table.Buffer techinque was making my query run incredibly slowly and adding the row count column didn't seem to do anything.

 

I also tried changing the merge type to a Full Outer (full rows from both tables) then filtering out the rows added from doing the Right Outer part of the merge. Came back with the same number of rows 😞

 

This is in Excel Power Query (on Office 365) rather than in Power BI but the principles should be the same.

ApuriceNexone
Frequent Visitor

Re: Power Query - Row disappear after expand column

Did you ever found a solution?
jdusek92 Helper II
Helper II

Re: Power Query - Row disappear after expand column

Hello, 

I have been told that this this "this behavior is intentional. Operations like “distinct” and “merge” do not guarantee that an input sort order is preserved. This is because in cases when we run the operation against a relational database, the database itself does not preserve the sort order."

 

Here is some help text about it: https://docs.microsoft.com/en-us/power-query/commonissues#preserving-sort


Personally I don't quite understand nor argee how this is an intentional behavior, when a preview in one step is showing different values then in the next step.
Seeing steps in the right pane is giving anybody a false understanding of how power query process data: Not in sequentional steps as seen in the steps pane, but in a different hidden way that cannot be predicted.

This feature has already caused me a lot of trouble. So be aware!

 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Attending MBAS? Claim your badge

Attending MBAS? Claim your badge

Whether you’re streaming the digital event live, or watching on-demand, claim your attendee badge to sport on your profile.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors