Passing parameters as a data source path to other table

[gil12]

Hello,

I have parameters table, it basically had index (integar column) and path (data source path). Now I'd like to pass a value from a path column to any particular table based on index column. My sources path, file names etc changes quite often and multiple dashboard consumes these sources. So i'd like to manage this parameters table to update paths. 

Has anyone done something like that? Previously i had function created where you can pass table name and index and it'll pick up a source path but when uploading dashboard to services, got a error since it have to have a direct connection to sources.

Does anyone have a better solution? if not could anyone help me with above question?

@Greg_Deckler 

Thanks in advance

Let

Source = Excel.Workbook(File.Contents("path from above table based on provided Index"), null, true),

[v-alq-msft · Accepted solution]

Hi, @gil12 

Based on your description, I'd like to suggest you use ‘Manage Parameters’ in Query Editor to parameterise a Data Source. 

You may create a parameter with a list of data source path names and a parameter with a list of table names as below.

Then you need to create a new query with the following code in 'Advanced Editor'.

When p1 = '...\Client A-Sequence number' and p2 = 'ClientA', here is the result.

When p1 = '...\Client B-Sequence number' and p2 = 'ClientB', here is the result.

For further information, please refer the article .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gil12]

Thank you! I think this will work for me.

[parry2k]

@gil12 you can do something like this before source

let
 myIndex = 1,  
 myPath = Table.SelectRows(#"Your path table name", each [Index]=myIndex){0}[path],

and you can use myPath in source 

I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[gil12]

Hi there, 

Get following error when passing your suggested parameters. Also when uploading it to services and setting up Gateway, i'm expecting an error saying "Power bi requires absolute path to the data sources". Can you please help?

If you have any solution for my problem, i'd appreciate that as well! Basically my sources changes often and I'd like one table where i upload path and all dashboard get updated with new paths.

Error snip below:

[parry2k]

@gil12 this is a different issue, check details at this link.

[gil12]

Do you suggest that I’d do something differently for access source path for my tables from one table?

[parry2k]

@gil12 no it is a data privacy issue, did you read the post I mentioned earlier.