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