Forum Discussion
Efficient way to conditionally transform nested tables with different structures.
- 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 _ }}))),
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 _
}}))),
- Anonymous2 years agoNot applicable
Hi scottdk
It is glad that you find the good solution, you can mark it as a solution so that more users can refer it.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.