Forum Discussion
New Table with OR Logic from Multiple Columns
Hello,
I would like to make a new table in the Power Query step based on OR logic referencing multiple columns from the original table, and have not been able to figure out how to do so.
For example, my original table is as follows:
| PersonID | Major1 | Major2 |
| 10000 | A | |
| 20000 | B | A |
| 30000 | A | |
| 40000 | C | B |
I would like to make a new table of rows with the logic that if major1=A OR major2=A, then include the row in that table.
In the actual data, I would be referencing close to 50 columns, so simply creating a new Conditional Column with the logic and then filtering the new table based on the Conditional Column would not be practical. So, essentially I'm looking for a way in the M code to say =OriginalTable IF Major1=A OR Major2=A.
Thank you!
I solved my own question after playing around with it for awhile:
In PowerQuery, I created the new table as a copy of the old table. Then, I filtered the column Major 1 to =A to generate the code for the step as follows:
= Table.SelectRows(#"Added Conditional Column", each ([Major 1] = "A"))
Then in the Advanced Editor, changed it to the following:
= Table.SelectRows(#"Added Conditional Column", each [Major 1] = "A" or [Major 2] = "A")
which left me with a table of the rows I needed.
5 Replies
what is the source of the table in M?
The original table is an Excel Workbook, so:
= Excel.Workbook(File.Contents("[File Path]"), null, true)Unpivoting would create a ton of excess columns as there are quite a few different values, so I was also hoping to avoid that.
you could also unpivot all the columns you want to place the filter on and then do conditional formatting, then pivot them again?
unless you can filter excel directly easily, i would unpivot all the columns you want to place the filter on and then do conditional column, then pivot them again?
see example attached with pbix
I solved my own question after playing around with it for awhile:
In PowerQuery, I created the new table as a copy of the old table. Then, I filtered the column Major 1 to =A to generate the code for the step as follows:
= Table.SelectRows(#"Added Conditional Column", each ([Major 1] = "A"))
Then in the Advanced Editor, changed it to the following:
= Table.SelectRows(#"Added Conditional Column", each [Major 1] = "A" or [Major 2] = "A")
which left me with a table of the rows I needed.
