gistfile1.txt

let
  // Your Sherweb APIs configuration keys,                                          
  // to generate those please click on the 'Security' section on the Cumulus left side panel, then on click the subsection 'APIs' & follow the instruction to create a new key.                                                                                                                                                                             
  Client_ID = Client_ID_Param,
  Client_Secret = Client_Secret_Param,
  Subscription_Key = Subscription_Key_Param,
  // Year to star and end extracting invoice data.                                                
  // When you create your APIs KEy for the first time the System will generate invoices data for the last 36 months It may take a few yours to have those being generated.                                                                                                                                                                        
  // You also have the ability to access the current invoice data, of course this invoice will get updates.                                                                                                         
  // In this sample we haven't break the down the start/stop trigger at the month level.                                                                                      
  StartYear = StartYear_Param,
  EndYear = Date.Year(DateTime.LocalNow()),
  Charges_Acquisition = 
    let
      Years = Table.FromList(
        {StartYear .. EndYear},
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      AddMonth = Table.AddColumn(Years, "Month", each {1 .. 12}),
      ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
      CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Column1], [Month], 1), type date),
      #"Changed Type" = Table.TransformColumnTypes(CreateDate, {"Date", type text}),
      // URL Column             
      URL = Table.AddColumn(
        CreateToken,
        "URL",
        each "https://api.sherweb.com/distributor/v1/billing/payable-charges?date=" & [Date],
        type text
      ),
      // Fetching Token                 
      Authkey = Json.Document(
        Web.Contents(
          "https://api.sherweb.com/auth/oidc/connect/token",
          [
            Content = Text.ToBinary(
              Uri.BuildQueryString(
                [
                  client_id     = Client_ID,
                  client_secret = Client_Secret,
                  scope         = "distributor",
                  grant_type    = "client_credentials"
                ]
              )
            ),
            Headers = [
              #"Content-type"  = "application/x-www-form-urlencoded",
              Accept           = "application/json",
              #"Cache-Control" = "no-cache"
            ]
          ]
        )
      ),
      Parts = [token = Authkey[access_token]],
      Bearer_Token = Parts[token],
      CreateToken = Table.AddColumn(#"Changed Type", "Token", each Bearer_Token, type text)
    in
      // Acquiring API Data                     
      URL,
  Authkey = Json.Document(
    Web.Contents(
      "https://api.sherweb.com/auth/oidc/connect/token",
      [
        Content = Text.ToBinary(
          Uri.BuildQueryString(
            [
              client_id     = Client_ID,
              client_secret = Client_Secret,
              scope         = "distributor",
              grant_type    = "client_credentials"
            ]
          )
        ),
        Headers = [
          #"Content-type"  = "application/x-www-form-urlencoded",
          Accept           = "application/json",
          #"Cache-Control" = "no-cache"
        ]
      ]
    )
  ),
  Parts = [token = Authkey[access_token]],
  Acq_token = Parts[token],
  #"Renamed First Column to ""Year""" = Table.RenameColumns(
    Charges_Acquisition,
    {{"Column1", "Year"}}
  ),
  #"Added Custom Column ""API Output""" = Table.AddColumn(
    #"Renamed First Column to ""Year""",
    "API Output",
    each (
      Json.Document(
        Web.Contents(
          [URL],
          [
            Headers = [
              #"Content-type"              = "application/x-www-form-urlencoded",
              #"Accept"                    = "application/json",
              #"Ocp-Apim-Subscription-Key" = Subscription_Key,
              #"Authorization"             = "bearer " & Acq_token
            ]
          ]
        )
      )
    )
  ),
  // Removal of Entries Without Data                                  
  #"Duplicated ""API Output"" Column" = Table.DuplicateColumn(
    #"Added Custom Column ""API Output""",
    "API Output",
    "Existing Charges"
  ),
  #"Expanded ""Existing Charges""" = Table.ExpandRecordColumn(
    #"Duplicated ""API Output"" Column",
    "Existing Charges",
    {"charges"},
    {"Existing Charges Present"}
  ),
  #"Extracted ""Existing Charges"" Values" = Table.TransformColumns(
    #"Expanded ""Existing Charges""",
    {"Existing Charges Present", each Text.Combine(List.Transform(_, Text.From)), type text}
  ),
  #"Replaced Errors to [TRUE]" = Table.ReplaceErrorValues(
    #"Extracted ""Existing Charges"" Values",
    {{"Existing Charges Present", "TRUE"}}
  ),
  #"Remove [Empty] rows from ""Existing Charges Present""" = Table.SelectRows(
    #"Replaced Errors to [TRUE]",
    each [Existing Charges Present] <> null and [Existing Charges Present] <> ""
  ),
  // "API Output" as New Query and Data Model Construction                                                        
  #"""API Output"" as New Query" = #"Remove [Empty] rows from ""Existing Charges Present"""[
    API Output
  ],
  #"Converted ""API Output"" to Table" = Table.FromList(
    #"""API Output"" as New Query",
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Expanded Resulting Column" = Table.ExpandRecordColumn(
    #"Converted ""API Output"" to Table",
    "Column1",
    {"charges"},
    {"API Output Lists"}
  ),
  #"Expanded ""API Output Lists"" to Records" = Table.ExpandListColumn(
    #"Expanded Resulting Column",
    "API Output Lists"
  ),
  #"Expanded ""API Output Lists"" to Values" = Table.ExpandRecordColumn(
    #"Expanded ""API Output Lists"" to Records",
    "API Output Lists",
    {
      "productId",
      "productName",
      "sku",
      "chargeId",
      "chargeName",
      "chargeType",
      "periodFrom",
      "periodTo",
      "quantity",
      "listPrice",
      "netPrice",
      "netPriceProrated",
      "subTotal",
      "currency",
      "isBilled",
      "isProratable",
      "invoice",
      "tags"
    }
  ),
  #"Added Index" = Table.AddIndexColumn(#"Expanded ""API Output Lists"" to Values", "Index", 0, 1),
  #"Reordered Columns" = Table.ReorderColumns(
    #"Added Index",
    {
      "Index",
      "productId",
      "productName",
      "sku",
      "chargeId",
      "chargeName",
      "chargeType",
      "periodFrom",
      "periodTo",
      "quantity",
      "listPrice",
      "netPrice",
      "netPriceProrated",
      "subTotal",
      "currency",
      "isBilled",
      "isProratable",
      "invoice",
      "tags"
    }
  ),
  #"Expanded invoice" = Table.ExpandRecordColumn(
    #"Reordered Columns",
    "invoice",
    {"number", "date", "periodFrom", "periodTo"},
    {"number", "date", "periodFrom.1", "periodTo.1"}
  ),
  #"Split ""date"" by Delimiter" = Table.SplitColumn(
    #"Expanded invoice",
    "date",
    Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv),
    {"date.1", "date.2"}
  ),
  #"Changed ""date"" Type to date" = Table.TransformColumnTypes(
    #"Split ""date"" by Delimiter",
    {{"date.1", type date}, {"date.2", type time}}
  ),
  #"Removed ""date"" Column Slag" = Table.RemoveColumns(
    #"Changed ""date"" Type to date",
    {"date.2", "periodFrom.1", "periodTo.1"}
  ),
  #"Renamed ""date"" Columns" = Table.RenameColumns(
    #"Removed ""date"" Column Slag",
    {{"date.1", "date"}}
  ),
  #"Changed Type ""Quantity"" to number" = Table.TransformColumnTypes(
    #"Renamed ""date"" Columns",
    {{"quantity", Int64.Type}}
  ),
  #"Expanded ""tags"" to List" = Table.ExpandListColumn(
    #"Changed Type ""Quantity"" to number",
    "tags"
  ),
  #"Expanded ""tags"" from Records" = Table.ExpandRecordColumn(
    #"Expanded ""tags"" to List",
    "tags",
    {"name", "value"},
    {"name", "value"}
  ),
  #"Pivoted ""tags"" Column" = Table.Pivot(
    #"Expanded ""tags"" from Records",
    List.Distinct(#"Expanded ""tags"" from Records"[name]),
    "name",
    "value"
  ),
  #"Split ""periodFrom"" by Delimiter" = Table.SplitColumn(
    #"Pivoted ""tags"" Column",
    "periodFrom",
    Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv),
    {"periodFrom.1", "periodFrom.2"}
  ),
  #"Changed ""periodFrom"" Type to date" = Table.TransformColumnTypes(
    #"Split ""periodFrom"" by Delimiter",
    {{"periodFrom.1", type date}, {"periodFrom.2", type time}}
  ),
  #"Removed ""periodFrom"" slag" = Table.RemoveColumns(
    #"Changed ""periodFrom"" Type to date",
    {"periodFrom.2"}
  ),
  #"Split ""periodTo"" by Delimiter" = Table.SplitColumn(
    #"Removed ""periodFrom"" slag",
    "periodTo",
    Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv),
    {"periodTo.1", "periodTo.2"}
  ),
  #"Changed ""periodTo"" Type to date" = Table.TransformColumnTypes(
    #"Split ""periodTo"" by Delimiter",
    {{"periodTo.1", type date}, {"periodTo.2", type time}}
  ),
  #"Removed ""periodTo"" Slag" = Table.RemoveColumns(
    #"Changed ""periodTo"" Type to date",
    {"periodTo.2"}
  ),
  #"Renamed ""periodFrom"" & ""periodTo""" = Table.RenameColumns(
    #"Removed ""periodTo"" Slag",
    {{"periodTo.1", "periodTo"}, {"periodFrom.1", "periodFrom"}}
  ),
  #"Removed ""Index"" Column" = Table.RemoveColumns(
    #"Renamed ""periodFrom"" & ""periodTo""",
    {"Index"}
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Removed ""Index"" Column",
    {
      {"subTotal", type number},
      {"netPriceProrated", type number},
      {"netPrice", type number},
      {"listPrice", type number}
    }
  )
in
  #"Changed Type"