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
Anonymous
Not applicable

Data Cleaning Nested Table

Hello,

 

I'm having some trouble getting this data cleaned. It's a nested table, which I understand how to handle but I'm getting hung up on splitting out the Equipment type from the region. I have 80 years worth of data, each in it's own seperate table, with various equipment sub-categories for each year. 

 

I would like to have a result of Column Headers: Equipement Category, Month, Region, Value. 

 

My ultimate goal is to append the cleaned data and drop in a year variable. 

 

 

nullJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
Equipment Category #1nullnullnullnullnullnullnullnullnullnullnullnull
Europe550600650700750800850900950100010501100
Japan391069391119391169391219391269391319391369391419391469391519391569391619
North America459924459974460024460074460124460174460224460274460324460374460424460474
Korea933860933910933960934010934060934110934160934210934260934310934360934410
Taiwan834465834515834565834615834665834715834765834815834865834915834965835015
China376170376220376270376320376370376420376470376520376570376620376670376720
Rest of the World130233130283130333130383130433130483130533130583130633130683130733130783
Total325958832596383259688325973832597883259838325988832599383259988326003832600883260138
Equipment Category #2nullnullnullnullnullnullnullnullnullnullnullnull
Europe146715171567161716671717176718171867191719672017
Japan356836183668371837683818386839183968401840684118
North America194091945919509195591960919659197091975919809198591990919959
Korea8065.3333338115.3333338165.3333338215.3333338265.3333338315.3333338365.3333338415.3333338465.3333338515.3333338565.3333338615.333333
Taiwan36999.3333337049.3333337099.3333337149.3333337199.3333337249.3333337299.3333337349.3333337399.3333337449.3333337499.3333337549.33333
China1842.6666671892.6666671942.6666671992.6666672042.6666672092.6666672142.6666672192.6666672242.6666672292.6666672342.6666672392.666667
Rest of the World239289339389439489539589639689739789
Total 71590.3333371640.3333371690.3333371740.3333371790.3333371840.3333371890.3333371940.3333371990.3333372040.3333372090.3333372140.33333
Equipment Category #3nullnullnullnullnullnullnullnullnullnullnullnull

 

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi @Anonymous

 

Below is the code on how I used the Query Editor to get it into the Shape you require.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVNaxRBEIb/yhCvIXR19VcdJXhR8CCCh5DDoqNZSHbiukH89/ZU9TvZguSme5h+3+dZtplhuvbm5uLdz6f948N8OE3Xu9P8Yzn+md7QxeXF4en+/t8ut5d9t6fj8jh3knPo1xL0qrlqrpqb5qZZNItmCsEWa73qr77fPe4OHbBQKGKBCAEkgkQQBmGQBJJAMkgGKZ2su35cjqe76e3DfNx/3XWVskhMI1QN/f4iAgiBEEgEiSAMwiAJJHWy7v5hOc7rrsLcSrDQb3+EQVIgBBACIZAIEkEYhEES2ZP+vNv/1kfdOivZQiYEkAJSQCpIBWkgDURAZJAcOlm3vb7bH9ab5VqoBgsxIoAwCIMkkASSQTJIASkgNdrNfpp/nabl+3S6m6cvy/H+2/rOcYjMI7QRGIRBEkgCySAZpIAUkApSO9HHvZx26/nhmCW3NlLhLW2sbqxurG2sbUw2JoP1V5O3tDHqTA/sC+Mh/v/x0M9fXZdMtlgr1oq1aq1aa9aaNbEm2mI/an5O5KL3WcgWa9VatdasNWtiTbT1U2WLNaL24jwgSUFszbbm0fPoZfQyeh29jt5Gb6PL6JLFzYAWSr5i/ayNyDXnonPROXaOnUvOJeeyc9m58uzc/OgTV+QKX+sHNrnmHDlHzkXnonPsHDuXnEvO5c2dzx5qKV6V9WMvmpw3cU7OXQzJNefIOXIuOhedY+f42b02t/p31mvT/zDNrDlpTpqz5qy5aC6aq+ba5HkmTSugLGF7bJVKcs256lx1rjnXnBPn5Nz15+qac7S514YY/58hdvsX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"null" = _t, JAN = _t, FEB = _t, MAR = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"null", type text}, {"JAN", type number}, {"FEB", type number}, {"MAR", type number}, {"APR", type number}, {"MAY", type number}, {"JUN", type number}, {"JUL", type number}, {"AUG", type number}, {"SEP", type number}, {"OCT", type number}, {"NOV", type number}, {"DEC", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"null", "Details"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Details", "Details - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Details - Copy", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"Details - Copy.1", "Details - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Details - Copy.1", type text}, {"Details - Copy.2", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Details - Copy.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Equipment Number", each if [#"Details - Copy.2"] = 1 then "Equipment Category #1" else if [#"Details - Copy.2"] = 2 then "Equipment Category #2" else "Equipment Category #3" ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Details - Copy.1", "Details - Copy.2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Details", "Equipment Number"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns1"

And here is what the output looks like, and from here you can then use this to then pass it in for each year.

 

A-PBI Community.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

2 REPLIES 2
GilbertQ
Super User
Super User

Hi @Anonymous

 

Below is the code on how I used the Query Editor to get it into the Shape you require.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVNaxRBEIb/yhCvIXR19VcdJXhR8CCCh5DDoqNZSHbiukH89/ZU9TvZguSme5h+3+dZtplhuvbm5uLdz6f948N8OE3Xu9P8Yzn+md7QxeXF4en+/t8ut5d9t6fj8jh3knPo1xL0qrlqrpqb5qZZNItmCsEWa73qr77fPe4OHbBQKGKBCAEkgkQQBmGQBJJAMkgGKZ2su35cjqe76e3DfNx/3XWVskhMI1QN/f4iAgiBEEgEiSAMwiAJJHWy7v5hOc7rrsLcSrDQb3+EQVIgBBACIZAIEkEYhEES2ZP+vNv/1kfdOivZQiYEkAJSQCpIBWkgDURAZJAcOlm3vb7bH9ab5VqoBgsxIoAwCIMkkASSQTJIASkgNdrNfpp/nabl+3S6m6cvy/H+2/rOcYjMI7QRGIRBEkgCySAZpIAUkApSO9HHvZx26/nhmCW3NlLhLW2sbqxurG2sbUw2JoP1V5O3tDHqTA/sC+Mh/v/x0M9fXZdMtlgr1oq1aq1aa9aaNbEm2mI/an5O5KL3WcgWa9VatdasNWtiTbT1U2WLNaL24jwgSUFszbbm0fPoZfQyeh29jt5Gb6PL6JLFzYAWSr5i/ayNyDXnonPROXaOnUvOJeeyc9m58uzc/OgTV+QKX+sHNrnmHDlHzkXnonPsHDuXnEvO5c2dzx5qKV6V9WMvmpw3cU7OXQzJNefIOXIuOhedY+f42b02t/p31mvT/zDNrDlpTpqz5qy5aC6aq+ba5HkmTSugLGF7bJVKcs256lx1rjnXnBPn5Nz15+qac7S514YY/58hdvsX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"null" = _t, JAN = _t, FEB = _t, MAR = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"null", type text}, {"JAN", type number}, {"FEB", type number}, {"MAR", type number}, {"APR", type number}, {"MAY", type number}, {"JUN", type number}, {"JUL", type number}, {"AUG", type number}, {"SEP", type number}, {"OCT", type number}, {"NOV", type number}, {"DEC", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"null", "Details"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Details", "Details - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Details - Copy", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"Details - Copy.1", "Details - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Details - Copy.1", type text}, {"Details - Copy.2", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Details - Copy.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Equipment Number", each if [#"Details - Copy.2"] = 1 then "Equipment Category #1" else if [#"Details - Copy.2"] = 2 then "Equipment Category #2" else "Equipment Category #3" ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Details - Copy.1", "Details - Copy.2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Details", "Equipment Number"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns1"

And here is what the output looks like, and from here you can then use this to then pass it in for each year.

 

A-PBI Community.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Thank you! This is a new technique for cleaning a nested table, I've been using a different one that would not work in this instance. Appreciate the help. 

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.