Calculated table

[ChiRomeu]

Hello colleagues,

  I have a following table which I want convert it to:

  Could you please kindly help me to solve that?

Best regards

Chi

[adudani · Accepted solution]

hi @ChiRomeu ,

I used power query for this. 

create a blank query, copy and paste the below code into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTI0MjAwANIGhvpABBaM1QHLGgFFjUzQZI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", type number}, {"Date", type date}}),
    //create a list of months
    Month = Table.AddColumn(#"Changed Type", "Month", each {1..12} ),
    //expand the months
    #"Expanded Month" = Table.ExpandListColumn(Month, "Month"),
    //create a date using year and month
    #"Added Custom" = Table.AddColumn(#"Expanded Month", "Date_", each #date([Year],[Month],01)),
    //transform to look like output
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date_", type date}, {"Month", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date_", "Date"}})
in
    #"Renamed Columns"

let me know if this works for you.

thanks.

[adudani · Accepted solution]

hi @ChiRomeu ,

I used power query for this. 

create a blank query, copy and paste the below code into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTI0MjAwANIGhvpABBaM1QHLGgFFjUzQZI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", type number}, {"Date", type date}}),
    //create a list of months
    Month = Table.AddColumn(#"Changed Type", "Month", each {1..12} ),
    //expand the months
    #"Expanded Month" = Table.ExpandListColumn(Month, "Month"),
    //create a date using year and month
    #"Added Custom" = Table.AddColumn(#"Expanded Month", "Date_", each #date([Year],[Month],01)),
    //transform to look like output
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date_", type date}, {"Month", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date_", "Date"}})
in
    #"Renamed Columns"

let me know if this works for you.

thanks.

[ChiRomeu]

Dear Avinash,

 It works a half until expanded column "month", but as you can see the picture above, i want to something like when expanded to month meanwhile divided by 12 for the amount column.

Thanks

Chi

[adudani · Accepted solution]

Hi @ChiRomeu ,

Kindly add a column after the month lost step.

Use [amount] / List.Count([Month] in the step and then expand the month list.