Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm new at creating Dataflows and I'm running into an issue where if I try to filter a table off of a column (TableA[Column1]) from another table the query will timeout. However, if I run the same query but reference an actual entity that is a List (ListA). I need the list to be dynamic so when I update the table in Sharepoint it will update here as well. I've used the following syntax:
Works good:
OptA: Table.SelectRows(#"Navigation 1", each List.Contains(ListA, [ID]) = true)
OptB: Table.SelectRows(#"Navigation 1", each (List.Contains(ListA, [ID]) = true)) -- works but much slower
Does not work (timesout):
Opt1: Table.SelectRows(#"Navigation 1", each List.Contains(TableA[Column1], [ID]) = true)
Opt2: Table.SelectRows(#"Navigation 1", each (List.Contains(TableA[Column1], [ID]) = true))
Opt3: Table.SelectRows(#"Navigation 1", each (List.Contains(TableA[Column1], [ID])))
Opt4: Table.SelectRows(#"Navigation 1", each List.Contains(TableA[Column1], [ID]))
So I have been trying to turn TableA[Column1] into an actual List and in Power Bi desktop it will allow me to do so BUT in Power Query online with the Dataflow, everytime I turn the column into a list, Power query then converts back into a table. I need to be able to convert TableA[Column1] to a list or somehow to be able to filter another table by that column so it can be dynamic. I also tried to merge but that timesout as well.
Any ideas to get around this?
In case anyone else stumbles into this. I had the same issue of the dataflow converting my list to a table. I was able to prevent this by right-clicking the query and unchecking "Enable Load".
When you convert the table column into a list, is the table a single column table at that time or does it have other columns too? If the latter then it might be worth trying to change TableA into a single column table before converting it into a list.
I think you should raise a bug/issue report, this seems to be silly behavior (like a lot of the steps that the online Power Query editor is adding for no good reason).
How do you raise an issue?
It is a single column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.