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

[Kopec]

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

[artemus · Accepted solution]

let

Source = Excel.CurrentWorkbook(),

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

in

ActiveTable

[Kopec]

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]

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

....