Autogenerate Months rows with M code (?)

[tonijj]

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

[v-yuezhe-msft]

@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]

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?

[v-yuezhe-msft]

@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]

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 😞 

[v-yuezhe-msft]

@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

[tonijj]

Lydia, 

I tried, will try again, but have some comma token issues. I use EU keyboard and regional settings, so I replaced all commas with semi colons. 

Here is the original string, how would it look like combined with your code?


    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}})
in
    #"Changed Type"¨

[v-yuezhe-msft · Accepted solution]

@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

[tonijj]

Hi Lydia, 

It works, this is really a great solution, that I hope others will have use of as well! Thank you! 

Accepted as solution.