Forum Discussion

scottdk's avatar
scottdk
Frequent Visitor
2 years ago
Solved

Efficient way to conditionally transform nested tables with different structures.

I am looking for more efficient Power Query code to transform nested tables in a column where the structure differs depending on the record. In this simplified example, there are three types of reco...
  • scottdk's avatar
    2 years ago

    After a couple of days messing around with this on and off before posting my question I have found a method that doesn't involve adding and removing columns thanks to expanding on this post https://stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column 

    Code-wise I think METHOD_2 is a little easier to grasp, but the purist in me likes METHOD_3 not adding extra columns to clean up.

    Writing up the whole question with simpler examples than my real-world PDF mess gave me some insight and clarity on how all this works. 

     

    //////////////////////////////////////////////////////////////////////
    // METHOD_3 : Insert a new column and transform conditionally
    //////////////////////////////////////////////////////////////////////
    
    METHOD_3 = Table.FromRecords(Table.TransformRows( MAIN_TABLE, (r) =>  Record.TransformFields( r, {
    {"Data", each 
       if r[Id] = "Table004" then 
          Table.Skip(_, each [Column1] <> "Date")
    
       else if r[Id] = "Table006" then
          [  FirstRow       = Table.FirstN( _, 1 ),
             ReplaceRow1LF  = Table.ReplaceValue( FirstRow , "#(lf)", " ", Replacer.ReplaceText, Table.ColumnNames(_) ),
             RecombinedRows = ReplaceRow1LF & Table.Skip( _, 1), 
             PromoteHeaders = Table.PromoteHeaders( RecombinedRows ) 
          ][PromoteHeaders]
    
       else _
    }}))),