Forum Discussion
Helper IExpression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] o
Hi Toast36
Thank you for reaching out to the Microsoft Community Forum.
A special thanks to dufoq3 for responding on this topic.It looks like the issue arises from a cyclic reference when filtering the InventoryDimension table using the Sales table.
Try buffering the Sales table before using it for filtering:let
Source = Sql.Database("sql1.database.windows.net", "db1", [CommandTimeout=#duration(0, 5, 0, 0)]),
Sales = Table.Buffer(Source{[Schema="RPT", Item="Sales"]}[Data]),
InventoryDimension = Source{[Schema="RPT", Item="InventoryDimension"]}[Data],
SalesKeys = List.Distinct(Sales[Inventory Dimension Key]),
FilteredInventory = Table.SelectRows(InventoryDimension, each List.Contains(SalesKeys, [Inventory Dimension Key]))
in
FilteredInventory
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank You
Community ChampionHi Toast36,
check this:
let
Source = Sql.Database("sql1.database.windows.net", "db1", [CommandTimeout=#duration(0, 5, 0, 0)]),
InventoryDimension = Source{[Schema="RPT",Item="InventoryDimension"]}[Data],
Custom1 =
let
l = TableName[ColumnName], //i.e. Sales[Inventory Dimension Key] column which you want to filter by
res = Table.SelectRows(TableName,each List.Contains(l, [Inventory Dimension Key])) //TableName you want to filter
in
res
in
Custom1
Helper IThat seems to have worked from a code perspective, thanks, but has introduced another error
A cyclic reference was encountered during evaluation.
- dufoq3
So, you should provide samle data with full description of your issue. Don't forget to provide also expected result.
- Toast36
This is the code that is being used
Parallel running is disabled so the 500 Sales Table should run first. I want the 600 Inventory table to only show inventory items where there is a corresponding sale record as we have 11million inventory items which are not all relevant to the sales invoices