Forum Discussion

tonijj's avatar
tonijj
Helper IV
8 years ago
Solved

Autogenerate Months rows with M code (?)

Hi, 

 

I have a set of data with the following information;

 

  • Year
  • Supplier
  • Price
  • Service
  • Volume (quanitity)
  • Month

 

So, one row could be something like;    2018, Apple, 10(unit price) , MobilePhone, 5

 

Lets assume that our company pays Apple on a monthly basis, I want to be able to see the total cost per month; $10 x 5 = $50/month.

 

 

What I have in my dataset:

1 row per Service, per Year, per Supplier. 

 

Example: 

2018, Apple, 10 (unit price), MobilePhone, 5, 12 (month)

 

 

So, I could either create 11 x more rows per service item, price etc, which will generate thousands of rows. 


OR, is there are smart way to auto generate this in PowerBI? 

 

I managed to do this with help from this forum, to generate Days. See formula below. But since we are invoiced on a monthly basis it needs to generate rows with months instead of each day. 

 

List.Dates([Start],Duration.Days([End]-[Start])+1,#duration(1,0,0,0))

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    tonijj,

    The whole code is as follows.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Toni Johansson\OneDrive - Opticos AB\==Customers==\Customer\Next Gen\Price Model\Attachment 5.1 - Price Matrix 1.1 Scenario Dashboard.xlsx"), null, true),
    PriceBase_Table = Source{[Item="PriceBase",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PriceBase_Table,{{"SIN", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Price", Int64.Type}, {"Supplier", type text}, {"Version", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each #date([Year],[Month],31)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "StartDate", each #date([Year],1,1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Month.1", each Date.Month([Custom])),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Year", "Supplier", "Price", "Service", "Month.1", "Volume"}, {{"First Date", each List.Min([Custom]), type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"First Date"})
in
    #"Removed Columns"



    Regards,
    Lydia

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    tonijj,

    Add a blank query in Power BI Desktop, then copy the following code into Advanced Editor of the blank query and check if you get expected result.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUXIsKMhJBdKGBkDCNz8pMyc1ICM/DyRkChI2UorVASs2J0UxyGQnkChIYYAzkDDDMAy7fCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Supplier = _t, Price = _t, Service = _t, Volume = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Supplier", type text}, {"Price", Int64.Type}, {"Service", type text}, {"Volume", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each #date([Year],[Month],31)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "StartDate", each #date([Year],1,1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Month.1", each Date.Month([Custom])),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Year", "Supplier", "Price", "Service", "Month.1", "Volume"}, {{"First Date", each List.Min([Custom]), type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"First Date"})
in
    #"Removed Columns"




    Regards,
    Lydia

    • tonijj's avatar
      tonijj
      Helper IV
      8 years ago

      Hi Lydia, 

       

      Thank you! 

       

      It almost works. Here's whats happening/not happening:

       

      • It does not use the source data in my original file (the attached file was a scrubbed example file)
      • It adds Supplier "B", but should've been "Google" in the example file? 

       

      So, where/how would I change the code to fit my original file? Think this could be really helpful for other users if we together could produce a generic code with some simple instructions of Where and What to change for future users to change and use as well?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        tonijj,

        You can change the following source code to your own source code.

        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUXIsKMhJBdKGBkDCNz8pMyc1ICM/DyRkChI2UorVASs2J0UxyGQnkChIYYAzkDDDMAy7fCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Supplier = _t, Price = _t, Service = _t, Volume = _t, Month = _t]),



        Regards,
        Lydia