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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors