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 P...
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
Jakinta
Solution Sage
Solution SageHope 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
Thank You, Will try it out ! and If it works I'll promote it tas the solution !