Combining data with Year - Month rows

Hi jpt1228

I had a quick gap in my work and what I did was to first create a function in which I could get the data into the right format. As you can see below I added a new column which will keep the same row number for the same data. This enabled me to leverage this in the function.

Here is the function Code

(IndexNumber as number) =>
 let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTEktUvDJzEtVMFTSUUJHsTrRShlIaoxwqEE2xxiHGpfU4uSizIKSzPw8oFBAYnI2kArOrEoFUqF5mSXFQNrIwNBcQRfsFCjTCME0BhvjWZKaC1ZgBsSGFgr+USAabpUhQpERMYqMsSgyxOGzQRRCJgimKYJphjuEoO4yhtmFNYSMYSqQFaGHkAnc0SZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Integer-Divided Column", each [Index] = IndexNumber),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",3),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Description", "Pack", "Size", "Units"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Units"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Units"}})
in
    #"Renamed Columns1"

Then I passed this function to the data, based on the same row number so that it would iterate over the data. To do this I created an almost identical table but I was just left with the Index Numbers, where I then "Invoke Custom Function" from the Add Columns Ribbon

And here is the end result.

Hi jpt1228

I did the divide by 7 because there were 7 rows which for each group.

Then in the last 3 lines it was doing the following:

• I was replacing the Null value with 0
• I then removed the column called "Units"
• And then I renamed the column "Value" (which was from the Unpivot) to "Units"