Forum Discussion
Solved
Warehouse Utilised Capacity by Day
Hi everyone, been using PowerBI for quite a while now and finding it incredibly useful. I've been exploring getting information for the storage of materials in the warehouse for any period of tim...
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Total Qty", Int64.Type}, {"Holding Area", type text}, {"Holding Duration", Int64.Type}, {"01-01-2019", type any}, {"07-01-2019", type any}, {"13-01-2019", Int64.Type}, {"19-01-2019", Int64.Type}, {"25-01-2019", Int64.Type}, {"31-01-2019", Int64.Type}, {"06-02-2019", Int64.Type}, {"12-02-2019", Int64.Type}, {"18-02-2019", type any}, {"24-02-2019", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item", "Total Qty", "Holding Area", "Holding Duration"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Holding end date"}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Holding end date", type date}}, "en-IN"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Holding start date", each Date.AddDays([Holding end date],-[Holding Duration])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From([Holding start date])..Number.From([Holding end date])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Holding end date", "Holding start date"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}), #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Date", type date}}, "en-IN"), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Value", type number}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Item", "Total Qty", "Holding Area", "Holding Duration", "Date", "Value"}) in #"Reordered Columns"Hope this helps.
v-lili6-msft
Community Support
Community Supporthi, oxologic
I think the best way in power bi is unpivot the date columns.
https://radacad.com/pivot-and-unpivot-with-power-bi
Best Regards,
Lin
hi, thanks for your response. I do know how to unpivot the table. Essentially I get from here:
To here. This is the part which I want to get to. I'm trying to figure out myself. Seems like CROSSJOIN is the way to go. I just need to figure out how. Hopefully I can solve it on my own and help others out there with similar questions. This will provide me a good table to visualise the warehouse holding capacity vs the utilised capacity for various products, the quantity at any point in time broken down by defined zones.
- Ashish_Mathur
Super User
Hi,
It looks like it is the second table that you would like to generate. Am i correct?
Yes you are right. I'm trying to get to the second table.
- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Total Qty", Int64.Type}, {"Holding Area", type text}, {"Holding Duration", Int64.Type}, {"01-01-2019", type any}, {"07-01-2019", type any}, {"13-01-2019", Int64.Type}, {"19-01-2019", Int64.Type}, {"25-01-2019", Int64.Type}, {"31-01-2019", Int64.Type}, {"06-02-2019", Int64.Type}, {"12-02-2019", Int64.Type}, {"18-02-2019", type any}, {"24-02-2019", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item", "Total Qty", "Holding Area", "Holding Duration"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Holding end date"}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Holding end date", type date}}, "en-IN"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Holding start date", each Date.AddDays([Holding end date],-[Holding Duration])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From([Holding start date])..Number.From([Holding end date])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Holding end date", "Holding start date"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}), #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Date", type date}}, "en-IN"), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Value", type number}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Item", "Total Qty", "Holding Area", "Holding Duration", "Date", "Value"}) in #"Reordered Columns"Hope this helps.