Yes, you can use parameters to store them in the excel file.
Unfortunately they will be in plain text.
There is an example in the link I posted.
1 Like
@NateS what is the end goal again? Who is going to use this data connection or what is going to use this?
End goal is to display relevant data on big screens around the shop. Basic reports like department level productivity, upcoming jobs, whatever else we come up with.
The data in my examples are just for reference. We might display any number of reports here. Excel data can be used in PowerPoint, so that is the basic outline. However I am still hoping grafana will do this better.
1 Like
When I check the local version saved on my desktop, I was able to open the excel file, and refresh it without entering any credentials. Similarly, if I open the excel file from a network folder, I do get the initial message that external content is disabled. But once I enable it, and refresh, no credentials are required.
I guess it somehow remembers that I was the one that created the power query, and then it doesnāt ask me for credentials. Does anyone know how this works?
1 Like
I am not sure, but have experienced a similar interaction with the report Nate.
The credentials (I think) are stored on your computer for the connection. So, if you e-mailed that file to someone else in the organization it should prompt them. Once you authenticate I donāt think you have to again for that connection.
To Clear:
Go to the data tab and select āGet Dataā
Then select Data Source Settings.
Select the DataSource and clear the permissions using the drop down at the bottom of the screen.
4 Likes
In the Power Query editor, select āAdvanced Editorā towards the left on the Home tab. Note that each function step maps to the āAPPLIED STEPSā list on the right side of the query editor you were just in. Your connection URL and parameters should be right up towards the top of the list - look for credentials there. If you see them, you know that those credentials are stored as plain text in your file.
This is why itās super useful to keep a list of what API keys are used for what, and where those things are deployed. That makes it actually possible to rotate keys on a schedule without tearing out all of your hair, and feel righteously smug when someone complains their copy stopped working because you know they made a feral copy.
Standard PSA, donāt depend on Excel password protection to keep any secrets. Itās trivial to bypass, less than a minute if youāre handy with 7zip and a proper text editor.
2 Likes
The vba does store the password in plain text and always did, but I tried running the user:password thru base64encode and using the encoded string and it worked, so you could have that and the api-key in the macro.
4 Likes
Donāt make me beg. You still havenāt posted it.
1 Like
@klincecum
justForKevin.txt (3.8 KB)
3 Likes
In my case, I do not see user/pass details there in the advanced editor. I only see the API key. For example:
let
Source = Json.Document(Web.Contents("https://centralusdtapp01.epicorsaas.com/saas512/api/v2/odata/VTAERO/BaqSvc/OJEXForDash/Data?api-key=myapikeygoeshere")),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"SysCube_Dimension1", "SysCube_Dimension2Date", "SysCube_ValueDec01", "SysCube_ValueDec02", "SysCube_ValueDec03", "SysCube_ValueInt01", "SysCube_SysCharacter01", "RowIdent"}, {"SysCube_Dimension1", "SysCube_Dimension2Date", "SysCube_ValueDec01", "SysCube_ValueDec02", "SysCube_ValueDec03", "SysCube_ValueInt01", "SysCube_SysCharacter01", "RowIdent"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"SysCube_Dimension2Date", type datetimezone}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"SysCube_Dimension2Date", type date}, {"SysCube_ValueDec01", Currency.Type}, {"SysCube_ValueDec02", Currency.Type}})
in
#"Changed Type1"
Now that I have my data in Excel, I want to build up some charts. In my manual approach where I typed in the value each day, I had a sheet for each department. I would like to do this with the power query editor. I am wondering if I can take an existing power query, (like the one I posted above) and split it into multiple ātablesā by department. I know I could just create a new power query for each department, I am just curious if the power query editor will split a single query into multiple output excel sheets.
I just filtered the existing power query by the department. Then I renamed the query to match the department. I copied it a few times and just changed the filter for each copy. I also changed the names to match the departments. After closing and loading, all the tabs are in place! Perfect!
1 Like
You can reference queries from other queries. One way is to right-click the query and thereās a reference option. Another is to pop into advanced editor and say source = myQueryName. Then drop a filter on the department column, and maybe delete the sheet the main query created - the query itself will still get loaded behind the scenes. Pro tip for when you have queries referencing other queries: In Excel proper (not the query editor) right click each query and uncheck the āload in backgroundā checkbox to prevent parallel loading whoopsies.
1 Like
There are some gems in this post. Thanks for the contributions. Iām a bit late to the party here and it will take a bit for me to digest it all.
We had a need to replicate odbc based spreadsheets from our legacy system to BAQ based ones.
I struggled to get odata to work with single sign in just as noted having the credentials requirement on refresh and ended up going down the route of a module with a series of subroutines that performs passes through the credentials and apikey, runs the BAQ service then returns the data to a specified sheet and table. For the most part it works ok, It was definitely a lot of hassle.
3 Likes
I feel the same way. While itās all kind of jumbled, most of the pieces to do a lot
of things can be gleaned from this thread.
3 Likes