Forum Discussion
Helper IVAutogenerate 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,
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
tonijjHi 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?
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 :(