Forum Discussion
Extract values from tables dependent on table name
Hi all,
Very new to Power Query and M, but been attempting to learn about this topic before resorting to posting.
For example, what I am trying to achieve is I have a key table containing IDs item1, item2, item3, but this is simply a list of the items.
The information I want for each item is within its respective table; tables named item1, item2, item3.
Is it possible to lookup the value of the ID and match that to a table of the same name, to then extract values from a column within that table?
(An example is below)
Thanks in advance! 😆
e.g.
Table: KeyTable
Columns: ID
Item1
Item2
Item3
Table: Item1
Columns: Value
1
Table: Item2
Columns: Value
2
Table: Item3
Columns: Value
3
Desired Table
Table: MasterList
Columns:
ID Value
Item1 1
Item2 2
Item3 3
7 Replies
Hello stzlee
check out this code. Use Expression.Evaluate passing your column content and the #shared-option to ready dynamically from another query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}}), AddedCustom = Table.AddColumn(ChangedType, "Custom", each Expression.Evaluate([ID],#shared)[Value]), ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom") in ExpandedCustomIf this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy801,
Thank you for your response! I have been attempting to decipher your code, but sadly am getting nowhere due to the hex-encoding. I am assuming the "i45W8ixJzTVUio0FAA==" refers to Item1-3. Those were just an example and I am having trouble trying to alter that code into the actual table titles. In this case, the tables are actually named:
Table1: DeviceMaster
Column: ID
70B3D57050002A6B
70B3D57050002B64
and two tables with the "70B3..." as names. Could you please let me know how you translated "Item" into "i45W..."
Thanks,
Seb
Hello stzlee
try out this code to make things clearer.
let Source = #table ( {"ID"}, { {"Item1"} } ), ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}}), YourQueriesAsRecord= [Item1 = Item1], AddedCustom = Table.AddColumn(ChangedType, "Custom", (add)=> Expression.Evaluate(add[ID], YourQueriesAsRecord)[Value]), ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom") in ExpandedCustomHowever, as it was mentioned by artemus , my first code won't work after you loaded it to the datamodel
Therefore you need to maintain a record manually for every query you want to reference dynamically in this step
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I don't think using #shared will work. This is due to query isolation, where none of the other queries will be avialable unless explicitly referenced when you load the data into the model. It works fine in preview, but will probably not when you do close and load.
If possible you should try and load all your tables into one query, and then reference that query in other queries to extract out the individual tables.
Hi Jimmy801,
I have been trying to understand the replies, but because of the additional concepts that come with each reply (one example: "need to maintain a record manually for every query you want to reference dynamically in this step"), it feels like some sort of rabbit-hole. However, I am still attempting to learn all this when I find the time to.
Please be patient, I will mark the solution as solved when I do finally solve my problem.
Thanks,
Seb
