Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Greetings,
Here is my sample data:
Expected result:
What I need to accomplish is look at Column E first. If that column has a value of "Yes", then I look at Column B of that same record/row and grab its value. Once I get the target value of Column B, I will filter the table to show only all rows/records that match that collected value of Column B. From my example table, rows 6 and 10 had "Yes" as an answer in Column E, so, I grabbed their related values of Column B ("456" and "789", respectivaly). With those values at hand, I set my filter in Column B to just show all records that match those values. Column E is used to identify the target value of Column B, the filter itself is in Column B. All this must be done in POWER QUERY.
I have played with Custom Columns or Advanced Filters, but I cannot get it to work. Can someone help me with achieving what I need?
Thanks,
José
Solved! Go to Solution.
First thing that comes to my head is this..
Duplicate the Table and remove all but columns B and E and filter it to just "Yes"
Then, merge this table back to your main table using column B
Expand column E(from table 2) and rename it to make it a little more clear.
Use this new column and only filter for "Yes"
Insert this code where #"Changed Type" should be replaced with your previous step
= Table.Combine(List.Select(Table.Group(#"Changed Type", {"ColumnB"}, {{"All", each _}})[All], (x)=>List.Contains(x[ColumnE], "Yes")))
Complete test code in action for testing
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8sSS1S0lEyNDIGksGlxYl5QDo7JSs7BUgrxeoglJiYmoGUlKSWpQLptNTUwtRCmJrgxLwUAkoIGBOZWoyizNzCEkg65iWCFKWkpaDZhCqdkoZkQmJeZUlGZl46LkNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnD = _t, ColumnE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", Int64.Type}, {"ColumnC", type text}, {"ColumnD", type text}, {"ColumnE", type text}}),
#"Grouped Rows" = Table.Combine(List.Select(Table.Group(#"Changed Type", {"ColumnB"}, {{"All", each _}})[All], (x)=>List.Contains(x[ColumnE], "Yes")))
in
#"Grouped Rows"
First thing that comes to my head is this..
Duplicate the Table and remove all but columns B and E and filter it to just "Yes"
Then, merge this table back to your main table using column B
Expand column E(from table 2) and rename it to make it a little more clear.
Use this new column and only filter for "Yes"
It is definitely not the best solution in terms of performance and data duplication (on the Power Query side), but it certainly worked and achieved what I needed! Right now, the raw data is about 5K records so I can live with this for now. At least I can generate the preliminary report while I find the permanent solution. You are definitely a life saver! I absolutely appretiate your help. Thanks a lot!!!