Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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 

 

View solution in original post

3 REPLIES 3
jbwtp
Memorable Member
Memorable Member

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
Not applicable

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
Resolver II
Resolver II

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors