Forum Discussion
Combine and clean different excel sheets using PQ
Hi Anonymous ,
It'd be great if you could share the sample data for a better solution that will meet your expectation (Dynamic).
If not, please add a custom column after where you stop, then add the code below (I can only see one table from your screenshot and I assumed the second table or future table come in the same view):
Table.AddColumn(Table.RenameColumns(Table.Transpose(Table.FirstN(Table.Transpose(Table.SelectRows([Custom.3], each ([Column4] <> null and [Column4] <> "N/A") and ([Column3] <> null and [Column3] <> "N/A") and ([Column2] = "Basic" or [Column2] = "Variable"))),2)),{{"Column1", "Type"}, {"Column2", "Amount"}}),"Name",(x)=> [Custom.3]{0}[Column4])
Regards
KT
Hi Anonymous ,
It'd be great if you could share the sample data for a better solution that will meet your expectation (Dynamic).
If not, please add a custom column after where you stop, then add the code below (I can only see one table from your screenshot and I assumed the second table or future table come in the same view):
Table.AddColumn(Table.RenameColumns(Table.Transpose(Table.FirstN(Table.Transpose(Table.SelectRows([Custom.3], each ([Column4] <> null and [Column4] <> "N/A") and ([Column3] <> null and [Column3] <> "N/A") and ([Column2] = "Basic" or [Column2] = "Variable"))),2)),{{"Column1", "Type"}, {"Column2", "Amount"}}),"Name",(x)=> [Custom.3]{0}[Column4])
Regards
KT