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

Source; table ACC; several tables, select one if exist, if not select the second... TRABL

Hi,

I need to select a table from the list of names several tables in a fixed order. And it's possible that first tables do not exist.

I need to try the first name of the table and test if does exist, if does not exist then try if exists the second table. If the second table does not exist, then try if exists the thirt table........ 

I don't know how to write it to the Item ( ACC{[Schema="",Item=table]}[Data], 

 

I tried this (but with no correct result:)):

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Sloupec1", type text}}),

    Sloupec1 = #"Změněný typ"{0}[Sloupec1],

ACC = Access.Database(File.Contents("Z:\Data\Acc\ALL\"&(Sloupec1)&".mdb"), [CreateNavigationProperties=true]),

 

//First_tab = name of the first possible name of table

//Second_tab = name of the secont possible name of table

//Thirt_tab = name of the thirt possible name of table

 

Second_table = if (Second_tab)[HasError] = true then Thirt_table else Second_tab,

Thirt_table = if (Thirt_tab)[HasError] = true then Fourth_table else Thirt_tab,

Fourt_table = if (Fourth_tab)[HasError] = true then Error else Fourth_tab,

 

table = if (First_tab)[HasError] = true then Second_table else First_tab,

    Partner = ACC{[Schema="",Item=table]}[Data],

   

in

    Partner

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

let

Source = Excel.CurrentWorkbook(),

ActiveTable = (Source{[Name="Table1"]}? ?? Source{[Name="Table2"]}? ?? Source{[Name="Table3"]}?)[Content]

in

ActiveTable

View solution in original post

3 REPLIES 3
artemus
Microsoft
Microsoft

let

Source = Excel.CurrentWorkbook(),

ActiveTable = (Source{[Name="Table1"]}? ?? Source{[Name="Table2"]}? ?? Source{[Name="Table3"]}?)[Content]

in

ActiveTable

View solution in original post

Kopec
Frequent Visitor

Hi,

thank you, but I think that is not the reasult for my problem.

Part with Excel table works well, determine only name of Access db and path to it.

I have problem with determine name of the table in Access db.

For example:

In Access db can exist this tables 202104_offer ; _offer2021_all ; offer_all_backup

The best is open 202104_offer and don't try to open the others, but it may not exist.

If 202104_offer doesn't exist, try if exist _offer2021_all, if it exist, open it and stop trying open others, if it doesn't exist try open the offer_all_backup.

Offer_all_backup is allways exist, but it's really huge and openning it takes a very long time.

I need to modify this part:

Second_table = if (Second_tab)[HasError] = true then Thirt_table else Second_tab,

Thirt_table = if (Thirt_tab)[HasError] = true then Fourth_table else Thirt_tab,

Fourt_table = if (Fourth_tab)[HasError] = true then Error else Fourth_tab,

 

table = if (First_tab)[HasError] = true then Second_table else First_tab,

Partner = ACC{[Schema="",Item=table]}[Data],

   in

    Partner

 

Kopec
Frequent Visitor

Hi, 

sorry it works well. 

It need only modify to ACC table case:

....

Partner = (ACC{[Schema="", Item=First_tab]}? ?? ACC{[Schema="", Item=Second_tab]}? ??  ACC{[Schema="", Item=Last_tab]}?)[Data],

....

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors