cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leinad
Regular Visitor

can't apply changes when accessing tables using the #shared environment

Hi everyone,

 

I am trying to access some tables via their names as text using the #shared environment. Assuming for example I have the following two queries (called table1/table2 respectively):

let
  tab1 = Table.FromRecords({[CustomerID = 1, Name = "Bob", Phone = "123-4567"]})
in
  tab1
let
  tab2 = Table.FromRecords({[CustomerID = 4, Name = "Lisa", Phone = "576-5767"]})
in
  tab2

 

Now I want to acces them in another query like one of the following:

let
  first_table = Expression.Evaluate("table1", #shared)
in
  first_table
let
  first_table = Expression.Evaluate("table1", #shared),
  sec_table   = Expression.Evaluate("table2", #shared),
  res         = Table.Combine({first_table, sec_table})
in
  res

 

In preview it works just fine. However, when I am trying to apply the changes, I am getting the following error message when activating the first query: 

leinad_0-1664621947030.png

And this one when activating the second query:

leinad_1-1664622022274.png

 

I have also tried to use other methods to access the tables like Record.Field() or to use #sections[Section1] instead of #shared as an environment. Unfortunately, it always works in preview, but throws the respective error message when I am trying to apply the changes. The first error message seems to appear when I am only accessing one table, the second one when I am accessing multiple tables.

 

Would be great if you could help me out. 

 

 

1 ACCEPTED SOLUTION

@leinad Maybe try something like this:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if Type.Is(Value.Type([Value]), Table.Type) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@leinad I don't understand why you are using #shared here. There is no reason to do so. Just write your last two queries like this:

let
  first_table = table1
in
  first_table
let
  first_table = table1,
  sec_table   = table2,
  res         = Table.Combine({first_table, sec_table})
in
  res

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerThanks for your response. In this case i agree. However, this is just some dummy data to replicate the error. I just wanted to understand why the error messages occur.

 

The actual usecase is using a function to generate all the names of the tables which are to be combined and depends on a parameter for the number of tables. I hope that makes it a little bit clearer.

@leinad Maybe try something like this:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if Type.Is(Value.Type([Value]), Table.Type) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.