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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi sharmon9000,

Whta's your plan to split the table? We can split a column by Delimiter, please follow the steps in this article(Althrough it's used in excel, all steps are same in Power Query of Power BI). 

In addition, you create a slicer for the Current Week tab? Could you please share more details as Ashish_Mathur said? So that we can post solution which is close to your requirement.

Best Regards,
Angelia