Forum Discussion
Read a value from table A and get data from a column on another table B using M (powerquery)
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.
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 CustomTable 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
2 Replies
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 CustomTable 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