Forum Discussion

oxologic's avatar
oxologic
Frequent Visitor
7 years ago
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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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's avatar
oxologic
Frequent Visitor
7 years ago

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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

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

  • oxologic's avatar
    oxologic
    Frequent Visitor
    7 years ago

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      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's avatar
        oxologic
        Frequent Visitor
        7 years ago

        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])}),