Forum Discussion
Change referenced table based on cell value
Hi,
I've created the equivalent of an INDEX/MATCH to do a two-way lookup to find out which row and column to look up based on values of two other columns in my data set which looks like this:
each Record.Field(#"Employees, 2022"{[Users=[AUTHOR_NAME]]},[Target Column]))
This works fine, what I'd like to do however is change the table that is referenced (the bit in red) to be based on the values in another column - in my case, I have tables for past and future years named "Employees, 2016", "Employees, 2017", "Employees, 2018", etc. and a column of values (called 'Target Table') containing the name of the table I want to pull data from for that row.
In Excel, I'd just replace the bit in red with an INDIRECT and reference the relevant cell and it'll work fine, unfortunately, if an equivalent exists in Power BI, I've not been able to find it.
Before I just add an if/else statement to this for each year I'm trying to run it for, is there a way I can make Power BI change the table it's referencing based my column?
Any advice would be greatly received!
Thanks.
Hi Anonymous,
Try each Record.Field(Expression.Evaluate("#""" & "Employees, 2022" &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column])).
I guess, you know how/what to subsitute the "Employees, 2022" string with.
Be careful though, I think I had some issues with using #shared in the PBI online context (although it was Ok with Dataflows) with scheduled refresh. test it before deployment.
Kind regards,
John
3 Replies
Hi,
Here's example of possible solution. Sorry but I don't have to much time to clean it up.
I created 3 queries
query 1 named "Employees, 2022"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpJLEpPLQlLzEk1MjAyUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, TargetColumn = _t]) in Sourcequery 2 named "Employees, 2021"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPVdJRCkksSk8tCUvMSTUyMDJUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, TargetColumn = _t]) in Sourceand query with the solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXLNLcjJr0xNLdZRMDIwMlKK1YlWCsnPxZAxVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"AUTHOR_NAME]" = _t, TabeFrom = _t]), #"Added Custom" = Table.AddColumn(Source, "Table", each let tableF= [TabeFrom] in Table.SelectRows(Record.ToTable(#shared), each [Name]=tableF)), #"Expanded Table" = Table.ExpandTableColumn(#"Added Custom", "Table", {"Value"}, {"Table"}), #"Added Custom1" = Table.AddColumn(#"Expanded Table", "Output", each let auth=[#"AUTHOR_NAME]"] in Table.SelectRows([Table], each [Users]=auth)), #"Expanded Output" = Table.ExpandTableColumn(#"Added Custom1", "Output", {"TargetColumn"}, {"TargetColumn"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Output",{"Table"}) in #"Removed Columns"I'm using Record.ToTable(#shared) statement to create table with tables. Then I'm selecting correct table using Table.SelectRows.
Hope this helps.
Artur
Hi Anonymous,
Try each Record.Field(Expression.Evaluate("#""" & "Employees, 2022" &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column])).
I guess, you know how/what to subsitute the "Employees, 2022" string with.
Be careful though, I think I had some issues with using #shared in the PBI online context (although it was Ok with Dataflows) with scheduled refresh. test it before deployment.
Kind regards,
John
