Number of rows - Power Query

[Marialicepg]

Hey, guys!
In Power Query I shaped my data, which came from csv sources. I generated several queries and merged some of them. The problem is, when I apply these changes in Power BI, the queries have more rows than when I apply these changes in Excel, even using the same code. In Power BI more than 18000 rows are generated, while in Excel around 17000 rows are generated.

Can anyone tell me if Power Query works differently in Excel and Power BI? If Excel has any combination limiter, or something like that? 

[mahoneypat]

The query editor is the same between Excel and Power BI.  Did you copy the M code from one to the other to make sure they are exactly same (replace M code in Advanced Editor)?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Marialicepg]

Hello!

I'll explain better:

I have 2 PowerBi files, and I use csv files to supply them. File 1 is an exact copy of the Excel M code. This file contains the same number of rows as the Excel file. Pbix 2 is not an exact copy of Excel, but it should generate the same number of rows that is generated by the Excel M code, as the applied steps are very similar. The difference is in the number of steps. File 2 is "cleaner", I use fewer steps.

I'll give you an example:

In file 1 I used two steps to filter my table, as shown in the code below: 

First: Table.SelectRows(#"Change name", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

Second: Table.SelectRows(#"Filter applied", each not Text.StartsWith([Material], "MS") and not Text.StartsWith([Material], "S"))

In file 2 I used only one step to filter my table: Table.SelectRows(#"Change name", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) and not Text.StartsWith([Material], "MS") and not Text.StartsWith([Material], "S")and not Text.StartsWith([Material], "Material"))

I tought both codes should generate the same number of rows. But maybe I did something wrong and I cant see it. 

[Icey]

Hi @Marialicepg ,

Is what @mahoneypat  said the cause of your problem?

And have you checked whether the data after importing Power BI (at the step of #"Changed Type") has changed? 

Best regards

Icey

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

[mahoneypat · Accepted solution]

The 2nd one is also filtering out "Material" while the 1st is not.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Marialicepg]

The problem was the filter step, indeed. 

If I filter everything at once it doesn't work, but if I use 3 steps to filter my collumns it does work. 

Anyway, problem solved!

Thanks! 

[lbendlin]

CSV files are devious.  Especially when you have fields that contain carriage returns and/or line breaks .

In the CSV step click on the setup icon, and then select "Ignore quoted line breaks"  . And while you are at it, make double sure that the encoding matches your source and that the field delimiter is specified correctly.

[Marialicepg]

Thanks!

But the code is the same in Excel and Power BI. Both of them are set the same way.

The problem is that when I apply Power Query in Excel, it generates less rows than when I apply in Power BI.

[lbendlin]

Show your Power Query code for Power BI