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.

Ashish_Mathur's avatar
Ashish_Mathur
Super 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
    Super 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])}),

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        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.