Skip to content

Instantly share code, notes, and snippets.

@EricLacroix
Last active March 8, 2021 18:04
Show Gist options
  • Save EricLacroix/e6313e893f3f107f22eccfb79cddadb7 to your computer and use it in GitHub Desktop.
Save EricLacroix/e6313e893f3f107f22eccfb79cddadb7 to your computer and use it in GitHub Desktop.
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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment