Read a value from table A and get data from a column on another table B using M (powerquery)

[Anonymous]

Hello everyone, here is my scenario I have a fact table that needs one of its columns adjusted, I do not have direct access to the DB so I gotta do it through powerquery while importing the data to PBI.

Lets say my fact table (Table A) is : 

Store ID	store name	Date
1	Store A	01/01/2021
1	Store A	01/02/2021
2	Store B	01/05/2021
3	Store C	01/05/2021

And let's say after 01/02/2021 "Store A`s" name changed to "Megastore A"  So I want to keep it registered as "Store A" before that date and after that I want it to change to "Megastore A"

To do so I created an auxiliary Table B so I can input which stores changed names like so:

Original Store ID	New name	Date changed
1	Megastore A	01/02/2021

And in Table A (My fact) I created a new column (after a row filter step) containing the new storenames by the following M code:

= Table.AddColumn(#"Filtered Rows", "Adjusted store name", each if Table.Contains(#"Table B",[Original Store ID = [Store ID] ],"Original Store ID") then [New Name] else [store name])

But it doesn't work, however if I switch the argument "[New Name] " to an absolute value such as "check" it works, so I know it is reading table B and checking if my desired value is in there, but it is not retrieving the correspondent value from the next column of table B (thus retrieving my the New name). That is my first issue, aside from that I also want to include a datecheck, so i only returns the New name for rows where "Date" > 01/02/2021. So it would loook something like 

= Table.AddColumn(#"Filtered Rows", "Adjusted store name", each if Table.Contains(#"Table B",[Original Store ID = [Store ID] ],"Original Store ID") and [Date] >= [Date changed]  then [New Name] else [store name])

I barelly know M so I ask you what am I doing wrong and how may I make this work?

Thank You in advance.

[Jakinta · Accepted solution]

Hope this can help.

Just paste tables below in Advanced Editor as new queries and use lines you need.

Table A

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouyS9KVXAEsgwM9YHIyMDIUClWB5ukEULSCC7pBJU0xSdphpA0hks6Y+rElITpjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, #"store name" = _t, Date = _t]),
    Custom = Table.AddColumn(Source, "Adjusted store name", each let r=[Store ID], d=[Date], t= Table.SelectRows(#"Table B", each [Original Store ID] = r and [Date changed]<= d ) in if t[New name]={} then [store name] else t[New name]{0} )
in
    Custom

Table B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNTU8sLskvSlVwBPIMDPUNjPSNDIwMlWJ1opWMUBQ4QRWYIRQYoyhwRlMQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Store ID" = _t, #"New name" = _t, #"Date changed" = _t])
in
    Source

[Anonymous]

Thank You, Will try it out ! and If it works I'll promote it tas the solution !