Forum Discussion

stzlee's avatar
stzlee
Frequent Visitor
6 years ago

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 it...
Need Help
Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

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

  • stzlee's avatar
    stzlee
    Frequent Visitor
    6 years ago

    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

     

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      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

       

      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