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.

[GilbertQ]

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.

[jpt1228]

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.

[GilbertQ]

Hi @jpt1228

I am fairly certain that this can be done, I have worked with Excel files in some weird and wonderful formats in the past.

If you can create a sample table I can have a look for you?

[jpt1228]

@GilbertQcan you use the data set example I posted?

[GilbertQ]

Hi @jpt1228

I could potentially do that, but having an exact copy of your sample data in a table ensures that it should work when copied into your Power BI Desktop

[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.

[jpt1228]

Hello @GilbertQ It has taken me a bit to try to decipher the code and what steps you took. 

Can you help me out with this?

This is how I read your code in Red

Im fairly new to PBI, but your steps look like they work. I just don't understand all the steps.

1) Open Source

(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), Added index into new column
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}), Divided index by 7
    #"Filtered Rows" = Table.SelectRows(#"Integer-Divided Column", each [Index] = IndexNumber),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",3), Removed top 3 rows
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Index"}), Removed columns (Not sure which ones)
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]), Promoted Headers
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Description", "Pack", "Size", "Units"}, "Attribute", "Value"), Unpivoted Description, Pack, Size, Units Column 
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}), Renamed Attribute Column to "Date"
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}), Changed "Value" column to a number
    #"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"

[GilbertQ]

Hi @jpt1228

You are indeed correct.

The only one where you were unsure is where I removed the column called "Index"

[jpt1228]

Hi @guava a fe thanks and just a few more questions - I don't follow why you divided the index column by 7?

The last 3 steps I'm not sure on. 

    #"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"}})

Thanks

Jon

When you say you pass this function to the data you mean run your 

[GilbertQ · Accepted solution]

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"