Combining data with Year - Month rows

[jpt1228]

Hello, I have multiple data files that are delivered on a quarterly basis. The supplier will not change the format. There are several lines of header and then the data begins in the rows, the date is in Year - Month Number in the columns. Then the second data file is below that with the same headers and next  3 time periods Years - Months. I was deleting the top rows and unpivoting the 3 date columns but the  query editor is combining the 2017 - 1 with the 2017 - 4 thus I only have 2017 - 1, 2017 - 2, and 2017 - 3 for dates. The bottom ones don't come in correctly. Hopefully my explaination is clear.

I need to report this data that will be for 2017-1, 2017-2, 2017-3, 2017-4, 2017-5,2017-6 and then add in every 3 months when the new data is delivered.

[GilbertQ · Accepted solution]

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.