Forum Discussion

charlessrima's avatar
charlessrima
Copper Contributor
Jul 26, 2024

Adjusting file path in power query so other users can refresh queries

So I have a file for work that I've developed and I need to configure it so that others can refresh it.  We use Box for file sharing.  The problem is that when others open the file up - the source filepath is always tied to my unique user id.  I need to adjust the file path somehow so that it accounts for the fact that someone else is refreshing it.

 

Current file path syntax: C:\Users\MYUSERID\Box\Box_folder_1\Box_folder\2\box_folder_3\box_folder_4

I need the syntax to be: C:\Users\ANYUSERID\Box\Box_folder_1\Box_folder\2\box_folder_3\box_folder_4

 

I assume there is something I can do in the editor to make the file path something that is more relative, for example - adjusting the USER ID, or just giving the filepath a particular name so that it isn't looking for my specific User ID.  Any thoughts?

excel
power bi

1 Reply

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 26, 2024

    charlessrima 

    To get current UserID that's with VBA.

    You may add it as parameter and each user will adjust the named cell with userId value

    To use in Power Query

    let
    Source = Excel.CurrentWorkbook(){[Name="UserID"]}[Content][Column1]{0},
    pFilepath = "C:\Users\"
        & Source
        & "\Box\Box_folder_1\Box_folder\2\box_folder_3\box_folder_4"
    //...

     

    Another possible option is to connect file directly on the cloud. I'm not familiar with Box, standard approach is to use From Web connector using file URL. In case of Box it shall be direct download link Configuring Individual Shared Link Settings – Box Support , i.e. one which has .xlsx at the end of URL

Resources