Forum Discussion

Responsive Resident

9 years ago

Solved

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

Need Help

GilbertQ

9 years ago

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
9 years ago
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"

GilbertQ

Super User

9 years ago

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

Super User

9 years ago

Hi jpt1228

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"

And here is the end result.

jpt1228

Responsive Resident

9 years ago

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
Super User
9 years ago
Hi jpt1228

You are indeed correct.

The only one where you were unsure is where I removed the column called "Index"
- jpt1228
  Responsive Resident
  9 years ago
  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

Forum Discussion

Combining data with Year - Month rows

Related content

Data Combining Problem

Combining Column Data

Underlying Error Privacy...combining of data

Tables combine

Medallion Architecture - best practices to combine data sources