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) ,...
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

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

    • tonijj's avatar
      tonijj
      Helper IV
      8 years ago

      Hi again Lydia, 

       

      Apprecaite the fast response!

       

      So, if we assume (safely...) that Im not used to M code, at all. Could we "dummify" this and guide me a bit more? 

       

      I mean, I wouldnt even know where to start to change in that string :( 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        tonijj,

        You have added a new blank query , paste the code I provide and get expected result, right?

        If so, connect to your own data source Power BI Desktop, right click the table and select "Edit Query", then you will be navigated to Query Editor of Power BI Desktop, click Advanced Editor of yout current query, you would be able to see the source code of  your query. Copy the source line and replace the source line with the copied source code in the blank query you create.



        Regards,
        Lydia