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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors