Forum Discussion
Autogenerate Months rows with M code (?)
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,
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
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"¨
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,
LydiaHi Lydia,
It works, this is really a great solution, that I hope others will have use of as well! Thank you!
Accepted as solution.