Change referenced table based on cell value

[Anonymous]

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.

[jbwtp · Accepted solution]

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 

[Anonymous]

John, that's perfect!  I've got a column called 'Target Table' so just added that in as follows

each Record.Field(Expression.Evaluate("#""" & [Target Table] &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column]))

Thanks for your help.

Rob

[artpil]

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
Source

query 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
    Source

and 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