Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help with integrating parameters into String creation

My first post here. I've tried searching quite a bit and took a Udemy course to try to learn a bit more about M language but still a little stuck.

 

I am trying to create an API call to get customer-related data into a table. It requires a bearer token.

 

  • I created a parameter that successfully retrieves the token value/string called "ChoiceBearerTokenGet". 
  • My problem is two-fold.
    1. When I hard code the bearer into the new query (CitrixServiceLic - pasted below in red), it works great (although a slight timing issue once time enough time has expired, detailed in #2 below). I am asking for assistance on how to replace the hard-coded value (bold red below) with the parameter I created called "ChoiceBearerTokenGet".
    2. The bearer token is only good for a particular length of time and then expires. I also need to figure out how to force the refresh of "ChoiceBearerTokenGet" before the query below runs (so that it gets a new bearer token value in the parameter)

Here's the query I have built -- also in the picture


 

 

let

Date = Date.ToText(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),Date.Day(DateTime.LocalNow())),"yyyy-MM"),
Source = Json.Document(Web.Contents("https://licensing-eastus-release-b.citrixworkspacesapi.net/[companyName]/licenseusages/deployments/c..."&Date,
[Headers=[Authorization="CwsAuth Bearer=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyX2lkIjoiMzQ4MTcwN", Accept="application/json", #"Content-Type"="application/json"]])),
singleTenantsUsage = Source[singleTenantsUsage],
#"Converted to Table" = Table.FromList(singleTenantsUsage, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"customerId", "orgId", "displayName", "editionName", "totalCommitCount", "totalUsageCount", "totalOverageCount", "totalUsagePercent"}, {"Column1.customerId", "Column1.orgId", "Column1.displayName", "Column1.editionName", "Column1.totalCommitCount", "Column1.totalUsageCount", "Column1.totalOverageCount", "Column1.totalUsagePercent"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.displayName", "Company"}, {"Column1.editionName", "sku"}, {"Column1.totalCommitCount", "totalCommitCount"}, {"Column1.totalUsageCount", "totalUsageCount"}, {"Column1.totalOverageCount", "totalFlexCount"}, {"Column1.totalUsagePercent", "totalUsagePercent"}, {"Column1.orgId", "CitrixCompanyorgId"}, {"Column1.customerId", "CitrixCustomerId"}})
in
#"Renamed Columns"

 

Any help would be greatly appreciated!

 

Thank you!

Rob

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Paul - first, thank you for the reply. I had checked out that solution however the example in question is a key/value pair of Authorization/authkey

       

      my problem is I need to append to the authkey (authkey = "CwsBearer = " then a very long key (that I'm storing in a parameter).

       

      Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"],

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 


    I doubt you can add the parameter within the " " string.

     

    Try add the entire authkey to the parameter, then you can just call out the parameter in the code e.g.

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Anonymous - so I actually used the code below and it seems to work although I'm getting a new error stating "Formula.Firewall: Query 'CitrixCVADServiceLic' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination." Now I did some research on this and found this but I don't believe that applies to my situation or, rather, I don't know how. 

       

      The "ChoiceBearerTokenGet" is a parameter that grabs the token .

       

       

       

      let
    myBearerVariable = ChoiceBearerTokenGet,
    Date = Date.ToText(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),Date.Day(DateTime.LocalNow())),"yyyy-MM"),
    myUrl = "https://licensing-eastus-release-b.citrixworkspacesapi.net/ChoiceSoluti/licenseusages/deployments/cloud/memberships/csp/products/cvad?date="&Date,
    myTokenHeader = "CwsAuth Bearer=" & myBearerVariable,
    myBody = "{
                ""authorization"": """& myTokenHeader & """,
                ""content-type"": ""application/json"",
                ""Accept"": ""application/json""
            }",
    Source = Json.Document(
            Web.Contents(
                myUrl,
                [
                    Headers = Json.Document(myBody)
                ]
            )
    ),
    singleTenantsUsage = Source[singleTenantsUsage],
    #"Converted to Table" = Table.FromList(singleTenantsUsage, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"customerId", "orgId", "displayName", "editionName", "totalCommitCount", "totalUsageCount", "totalOverageCount", "totalUsagePercent"}, {"Column1.customerId", "Column1.orgId", "Column1.displayName", "Column1.editionName", "Column1.totalCommitCount", "Column1.totalUsageCount", "Column1.totalOverageCount", "Column1.totalUsagePercent"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.displayName", "Company"}, {"Column1.editionName", "sku"}, {"Column1.totalCommitCount", "totalCommitCount"}, {"Column1.totalUsageCount", "totalUsageCount"}, {"Column1.totalOverageCount", "totalFlexCount"}, {"Column1.totalUsagePercent", "totalUsagePercent"}, {"Column1.orgId", "CitrixCompanyorgId"}, {"Column1.customerId", "CitrixCustomerId"}})
in
    #"Renamed Columns"