Hi,
I have a query pulling data from a folder of 20-30 workbooks, in those workbooks are between 10-15 columns that I need to clean up, these columns all start with "AM/M" I have a representation of the code below (with a few more nested ifs), how can I run this without having to manually add a custom column for eac AM/M and handle varying numbers of columns.
thanks
John
if [#"AM/Mxxx)"]=null then
null
else if Text.StartsWith([#"AM/Mxxx"], "<") then
Number.From(Text.Middle([#"AM/Mxxx"], 1)) / 2
else if Text.StartsWith([#"AM/Mxxx"], "Not") then
0
else if Text.StartsWith([#"AM/Mxxx"], "Detected") then
1
else
Number.From([#"AM/Mxxx"])
Difficult to visualise exactly what you are dealing with, but my gut feeling says that you could unpivot the data first and then use the “Attribute” column (probably) to test if the text in it starts with “AM/M” and if so then do the Number.From part. And then pivot all back.
Not sure though. Can you upload an example of a file with the real column names and some fake data?
Added file with identifying details removed, and I lied I just noticed Columns AB:AM will also need cleaning but they dont start with AM/M
Aha! Then please explain all the logic to be applied.
Edit: Perhaps the attached file does what you need.
