Forum Discussion

sharmon9000's avatar
sharmon9000
Helper I
8 years ago
Solved

Current Week Companies List on Billing Report

Currently we load our billing report (an excel sheet) into PowerBI.  We only have a small selection of clients that get billed, not every client has a bill every week. The report currently, now has t...
Need Help
  • smpa01's avatar
    smpa01
    8 years ago
    Latest Week- Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "Latest Week. AMT"}})
in
    #"Renamed Columns"
    YTD Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "YTD. AMT"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Company "},Table1,{"Company "},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Latest Week. AMT"}, {"Latest Week. AMT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Latest Week. AMT] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Latest Week. AMT"})
in
    #"Removed Columns"
sharmon9000's avatar
sharmon9000
Helper I
8 years ago

Zubair, thanks for the quick response.  How would I split the table?  Currently I just have a column that checks to see if the invoice date/week is the same as the current week.  I then use that in a filter for the Current Week tab.  The Year to Date tab is the same visual not including the Current week filter, so it show everything. Sorry for the confusion about being multiple tables.

 

 

thanks

smpa01's avatar
smpa01
Community Champion
8 years ago
Latest Week- Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "Latest Week. AMT"}})
in
    #"Renamed Columns"
YTD Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "YTD. AMT"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Company "},Table1,{"Company "},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Latest Week. AMT"}, {"Latest Week. AMT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Latest Week. AMT] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Latest Week. AMT"})
in
    #"Removed Columns"