Dataflow Converting List back into a Table

Bi2thelly · ‎09-29-2021

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?

Mogsetsu · ‎06-09-2023

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".

lbendlin · ‎09-30-2021

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).