Using Windows environment variable in a PowerBI desktop parameter

[vertokite]

Hello

I'm trying to use the windows environment variable %USERNAME% as part of a parameter to define source setting.

The source data line in the editor looks like this:  source = Excel.Workbook(File.Contents(#"targetfc" & "\xxxxx.xlsx"), null, true),

I have tried to use forward and backward slashes. I keep getting the same error:

Is it even possible to use the windows environment variables within PowerBI desktop?

I appreciate your ideas, Thanks, Verto

[ArtiSt]

You may try somthing like 

let
re=(string,regex,flags)=>Web.Page(
"<script>
document.write(('"&string&"').match('"&regex&"','"&flags&"'));
</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in 
re(Text.Combine(Folder.Contents("C:\Users")[Name], ","),"p\\d{6}", "g")

 With RegEX you may filter the part of the path from the CSV generated by Text.Combine

see: RegEx in Power BI and Power Query in Excel with Java Script – (thebiccountant.com)

[Rifter]

I have yet to get this to work right.  I don't know why we can't put in %USERNAME% to dynamically shift document locations.  I have hundreds of students that access a PBIX file that is created from an Excel file each sememster.  Getting them to know where to put the excel file is very painful.

What I do, is I create two parametmers.  FilePath and Filename.  I put them in a group called File Location. (1)

Curernt Value for FilePath is C:\ITM310  Filename is name of the excel file used to build the PBIX file.  Put a \ before the file name.  (In my instance the file name doesn't change, but the folder name does.)

Then, for the source (2) in each table, I go in and set the excel workbook to Advanced (3) and use the Filepath Parts.  I pull the parameters for FilePath and Filename.  It then puts them together: {FilePath}{Filename} .(4)

It's not the best method, but it is bette than nothing.  This is something I have been trying to rectify for a few years now.  

[marcelsmaglhaes]

Hey @vertokite  try type "=Environ("VariableName")" nn the "Current Value" field. 

***(replace "VariableName" with the name of the Windows environment variable you want to use).

Kind Regards,
Marcel

[vertokite]

Hi Marcel and many thanks for taking the minute to help me out.  Since I'm still kind of a beginner I'm not entirely sure to understand your suggestion.  What would I type in the parameter for the filepath:  

=Environ("USERPROFILE")\PowerBI\      which should resolve to     C:\user\"username"\PowerBI.

I cant get this to work.  Maybe I misunderstood you.  Thanks again