Difference between PowerBI and Powery Query in Excel with same data

[dean_rowntree]

Hello

I am trying to create a cumulative flow diagram in PowerBi, but my data is not giving me the results I expected. I recreated it in Power Query and got the result I expected.

Here is the code for the PowerBI query and resulting table:

let
    Source = tbl_DateWindow,
    #"Added Custom" = Table.AddColumn(Source, "Project name", each List.Distinct(tbl_Cards[Project name])),
    #"Expanded Project name" = Table.ExpandListColumn(#"Added Custom", "Project name"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Project name", "Count", each let
        project = Record.Field(_,"Project name"),
        start = Record.Field(_,"Start date"),
        end = Record.Field(_,"End date"),
        tbl = Table.SelectRows(tbl_Cards, each [Project name]=project and [Created At]>=start and [Created At]<end),
        count = Table.RowCount(tbl)
        in count, type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Cumul", each let
        project = Record.Field(_,"Project name"),
        start = Record.Field(_,"Start date"),
        tbl = Table.SelectRows(#"Added Custom1", each [Project name]=project and [Start date]<=start),
        cumul = List.Sum(tbl[Count])
        in cumul, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Count", "End date"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Project name"]), "Project name", "Cumul", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Start date", "Date window"}})
in
    #"Renamed Columns"

And here is the code from Power Query in Excel and resulting table:

let
    Source = tbl_DateWindow,
    #"Added Custom" = Table.AddColumn(Source, "Project name", each List.Distinct(tbl_Cards[Project name])),
    #"Expanded Project name" = Table.ExpandListColumn(#"Added Custom", "Project name"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Project name", "Count", each let
        project = Record.Field(_,"Project name"),
        start = Record.Field(_,"Start date"),
        end = Record.Field(_,"End date"),
        tbl = Table.SelectRows(tbl_Cards, each [Project name]=project and [Created At]>=start and [Created At]<end),
        count = Table.RowCount(tbl)
        in count, type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Cumul", each let
        project = Record.Field(_,"Project name"),
        start = Record.Field(_,"Start date"),
        tbl = Table.SelectRows(#"Added Custom1", each [Project name]=project and [Start date]<=start),
        cumul = List.Sum(tbl[Count])
        in cumul, type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Start date", "Project name", "Cumul"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[#"Project name"]), "Project name", "Cumul"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Start date", "Date window"}})
in
    #"Renamed Columns"

Can anybody see where I am going wrong because this is driving me bananas at the moment!

Thank you for reading!

[v-shex-msft]

HI @dean_rowntree,

I guest this issue may related to power query lazy evaluation, you can take a look at following links about this:

On lazy value evaluation order in Power Query and Power BI

Power Query M Primer (Part 5): Paradigm

Regards,

Xiaoxin Sheng

[dean_rowntree]

Thank you for the information, but I don't understand how the same code can produce different reults from the same source data, with the only difference being the application the code is executed in.

[v-shex-msft]

HI @dean_rowntree,

I also not so sure for the internal calculation logic, maybe you can submit a support ticket to get better support from dev team.

Regards,

Xiaoxin Sheng

[dean_rowntree]

Thank you for the suggestion, I may try that if I can figure out how to do so.

[sp8]

Did you ever figure this out? I'm having the same issue- I get 2000 lines when I run a query in Excel, but 100 lines when I copy and paste the query into PBI.