Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I recieve a monthly report that breaks at every account change, displays the new account value on a line, followed by a repeat of the header, and then followed by table values. I would like to combine all those Append those table but add the account header as a field.
My data looks something like this;
Account A
Hdr1 Hdr2 Hdr3
Val1R1 Val2R1 Val3R1
Val1R2 Val2R2 Val3R2
Account B
Hdr1 Hdr2 Hdr3
Val1R1 Val2R1 Val3R1
Val1R2 Val2R2 Val3R2
I would like to import to a single data table that looks like:
Acnt Hdr1 Hdr2 Hdr3
A Val1R1 Val2R1 Val3R1
A Val1R2 Val2R2 Val3R2
B Val1R1 Val2R1 Val3R1
B Val1R2 Val2R2 Val3R2
Suggestions?
Solved! Go to Solution.
Hi @I_Like_Pi
Are Hdr1, Hdr2 and Hdr3 always the same, or do they change from Account to Account?
I'd say the best place to do this tidying is in in the Query Editor
Create a blank query and paste this in using the Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVIrViVbySCkyVFAAkkZg0hgsGJaYYxgEFAbSRlDaOMgQIWUElTKCShmBpWAGO1HV4FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Account", each if Text.StartsWith([Column1], "Acco") then [Column1] else null ), #"Filled Down" = Table.FillDown(#"Added Custom",{"Account"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Account A" and [Column1] <> "Account B")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2", "Column1.4"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Hdr1] <> "Hdr1")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Account A", "Hdr1", "Hdr2", "Hdr3"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Account A", "Account"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Account ","",Replacer.ReplaceText,{"Account"}) in #"Replaced Value"
If you then click on the various Applied Steps you can see how it's possible to transform the data.
I haven't tried Phil's code but once you get it into one table with form:
Acnt Hdr1 Hdr2 Hdr3
A Val1R1 Val2R1 Val3R1
A Val1R2 Val2R2 Val3R2
B Val1R1 Val2R1 Val3R1
B Val1R2 Val2R2 Val3R2
you may want to consider unpivoting the HDR columns so that you end up with:
Acnt Hdr Value
A 1 Val1R1
A 2 Val2R1
A 3 Val3R1
A 1 Val1R2
A 2 Val2R2
A 3 Val3R2
B 1 .......
etc.
Hi @I_Like_Pi
Are Hdr1, Hdr2 and Hdr3 always the same, or do they change from Account to Account?
I'd say the best place to do this tidying is in in the Query Editor
Create a blank query and paste this in using the Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVIrViVbySCkyVFAAkkZg0hgsGJaYYxgEFAbSRlDaOMgQIWUElTKCShmBpWAGO1HV4FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Account", each if Text.StartsWith([Column1], "Acco") then [Column1] else null ), #"Filled Down" = Table.FillDown(#"Added Custom",{"Account"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Account A" and [Column1] <> "Account B")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2", "Column1.4"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Hdr1] <> "Hdr1")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Account A", "Hdr1", "Hdr2", "Hdr3"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Account A", "Account"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Account ","",Replacer.ReplaceText,{"Account"}) in #"Replaced Value"
If you then click on the various Applied Steps you can see how it's possible to transform the data.
Came back to pick up this code again and realized I had not marked it as accepted.
thanks again Phil