Forum Discussion

dean_rowntree's avatar
dean_rowntree
Regular Visitor
7 years ago

Difference between PowerBI and Powery Query in Excel with same data

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!

5 Replies