Warehouse Utilised Capacity by Day

[oxologic]

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 time.

Right now, we have a table that looks something like this. So the products are planned by the date required. I know how to unpivot the date columns to get a date table connected to show when the product will be out of the warehouse / or required by the end user.

Item	Total Qty	Holding Area	Holding Duration	1/1/2019	7/1/2019	13/1/2019	19/1/2019	25/1/2019	31/1/2019	6/2/2019	12/2/2019	18/2/2019	24/2/2019
Product A	500	Zone 1A	90					300		200
Product B	2000	Zone 1B	90				500		500		500		500
Product C	250	Zone 1A	180			150		100
Product C	250	Zone 1B	30				100		150

Now the challenge is for me to get the information on the current holding in each zone by each day. Since the dates will convert into the "Holding End Date", I can easily minus the "Holding Duration" to get the "Holding Start Date". However, what I'll require is to show the total utilised capacity of the warehouse at any point in time.

From the data above, I should be able to see that from on 31 Jan 2019, the total utilised capacity should be 1850 across the various zones (assuming that the product is still held in the warehouse on the required by date). Is there some way to setup a new table with these information, which would meet my requirements? Ideally, my guess is that there will be a row for every day for every single item, replicating the qty as long as the date is between the start and end date.

If there's a better alternative to get the information required, that will be great as well!

[v-lili6-msft]

hi, @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

[oxologic]

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]

Hi,

It looks like it is the second table that you would like to generate.  Am i correct?

[oxologic]

Yes you are right. I'm trying to get to the second table.

[Ashish_Mathur · Accepted solution]

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.

[oxologic]

Great, thanks! it definitely works.

This is the magic part, but I don't understand it completely. Is the ".." code part of the M code, if so what does it function as and which part should i look under the documentations (https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference)? I understand the "{ }" part is a list, which is what enables the ExpandListColumn part.

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {Number.From([Holding start date])..Number.From([Holding end date])}),

[Ashish_Mathur]

You are welcome.  The part of the code creates a row for every date in the dae range.  The .. can be understood as "to" i.e. from the beginning date "to" the ending date.  I do not know which part of the document to look at.  I learnt this from someone else.