Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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: ...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    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 

     

artpil's avatar
artpil
Resolver II
4 years ago

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