Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_Like_Pi
Resolver II
Resolver II

Help Importing a badly formed report formatted as multiples reports 1 per account.

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?

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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

 

advanced editor.png

 

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4

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.

Phil_Seamark
Employee
Employee

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

 

advanced editor.png

 

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Came back to pick up this code again and realized I had not marked it as accepted. 

thanks again Phil

You're​ a beauty Phil. Thanks!

I wont have a chance to try it til I get back to the office Monday.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.