Forum Discussion
Using Windows environment variable in a PowerBI desktop parameter
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
4 Replies
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,
MarcelHi 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
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.
You may try somthing like
let
re=(string,regex,flags)=>Web.Page(
"<script>
document.write(('"&string&"').match('"®ex&"','"&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)
