cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stzlee
Frequent Visitor

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 7
Super User I
Super User I

Re: Extract values from tables dependent on table name

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
    ExpandedCustom

 

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

Super User IV
Super User IV

Re: Extract values from tables dependent on table name

Hi @stzlee 

 

You can a Column like below.

= Record.FieldValues( Record.SelectFields( #shared, [ID] ) ){0}

 image.png

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted
Microsoft artemus
Microsoft

Re: Extract values from tables dependent on table name

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.

stzlee
Frequent Visitor

Re: Extract values from tables dependent on table name

Hi @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

 

Super User I
Super User I

Re: Extract values from tables dependent on table name

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
    ExpandedCustom

However, 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

image.png

 

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

Super User I
Super User I

Re: Extract values from tables dependent on table name

Hello @stzlee 

have you been able to solve the problem with the replies given?

If so, 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

All the best

Jimmy

stzlee
Frequent Visitor

Re: Extract values from tables dependent on table name

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors