Transform row with year and value to multiple rows for months and value/12

[Anonymous]

Hello everyone,

I have done a lots of researches regarding the following requirement, but was not able to find a solution till yet.

In one table I have the columns ID, year, value and lots of other columns. It's looking like the following example.

ID | year | value | n columns

123 | 2020 | 1200 | xyz

132 | 2020 | 2400 | xyz

result should by

ID | date | value | n columns

123 | 01.01.2020 | 100 | xyz

123 | 01.02.2002 | 100 | xyz

...

132 | 01.01.2020 | 200 | xyz

132 | 01.02.2020 | 200 | xyz

Does anybody have a hint for me how to solve this with M?

Thank you guys 😉

[Anonymous]

Hi edhans, hi Jimmy,

thank you both very much for your help!

Solution is working perfectly for me! That was exactly what i needed.

Greetings, David

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this is a little different approach with List.Generate and calculates the first day of every month. I think the approach of @edhans is working as well, only that the number should be used for month instead of days.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjIwMgBShkYGBkqxOkBRYyOEqJEJSDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Year = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Months", (listcustom)=> List.Generate(()=> #date(listcustom[Year],1,1),each _ < #date(listcustom[Year]+1,1,1),each Date.AddMonths(_,1))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "MonthValue", each [Value]/12)
in
    #"Added Custom1"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans]

You can try this. You didn't really explain what you were doing. Is everything arbitrarly divided by 12, and it goes for the first 12 days?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjIwMgBShkYGIEqhorJKKVYHKGlshJA0MkGSjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID " = _t, #" year " = _t, #" value " = _t, #" n columns" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" value ", Int64.Type}, {" year ", Int64.Type}}),
    #"Added Numbers list 1-12" = Table.AddColumn(#"Changed Type", "Numbers", each {1..12}),
    #"Expanded Numbers list 1-12" = Table.ExpandListColumn(#"Added Numbers list 1-12", "Numbers"),
    #"Added Date" = Table.AddColumn(#"Expanded Numbers list 1-12", "Date", each #date([#" year "],1,[Numbers]), type date),
    #"Added New Value" = Table.AddColumn(#"Added Date", "New Value", each [#" value "] / 12, type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Value",{"ID ", "Date", "New Value", " n columns"})
in
    #"Removed Other Columns"

It turns this

into this

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need further help, please be clearer in how we get from point a to point b, and see the links below for providing data in a table format. Thanks!

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum