Forum Discussion
Combining data with Year - Month rows
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.
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.
10 Replies
Hi jpt1228
That can be done in the Query Editor by possibly changing the data shape so that you can get the dates in the right place. After which you can then pivot the data, or pivot it back to get it in the format.I have also done it in the past where I have used a function to pass the required information through.
If you cannot figure it out, please send through a sample file.
Hi GilbertQ that is my issue - I cannot unpivot the dates because it combines the first column 2017-1 and 2017-4 into 2017-1. I do not have the option of a data set that has the date in the columns for every item. What I really would like to do is process every data file seperately and then unpivot and append to the historical data to create a 2 or 3 year trend.
