Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- let
- // Filters
- current_date = DateTime.Date(DateTime.LocalNow()),
- end_date = Date.ToText(current_date,"yyyy-MM-dd") & " 23:59:59.000",
- initial_date = Date.AddMonths(current_date,-2),
- start_date = Date.ToText(initial_date,"yyyy-MM-dd"),
- filter = "origin="&Origin&"&start_date="&start_date&"&end_date="&end_date,
- //Connection to service
- GetTransactions = Web.Contents(Server, [ManualStatusHandling={204}, Headers=[Authorization="Bearer "&Token, ContentType="application/json"], RelativePath="/transaction?"&filter]),
- GetMetadata = Value.Metadata(GetTransactions),
- GetResponseStatus = GetMetadata[Response.Status],
- Output = if GetResponseStatus = 204 then "No Data" else GetTransactions,
- //Transformations
- GetTransactionsDocument = Json.Document(Output),
- #"Converted to Table" = Table.FromList(GetTransactionsDocument , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
- #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"code", "status", "date", "location", "details", "id"}, {"code", "status", "date", "location", "details", "id"}),
- #"Expanded details" = Table.ExpandListColumn(#"Expanded Column1", "details"),
- #"Expanded details1" = Table.ExpandRecordColumn(#"Expanded details", "details", {"type", "product", "hr", "qty", "net_sales"}, {"details.type", "details.product", "details.hr", "details.qty", "details.net_sales"}),
- #"Duplicated Column" = Table.DuplicateColumn(#"Expanded details1", "date", "date - Copy"),
- #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"date", "Datetime"}, {"date - Copy", "Date"}}),
- #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"details.net_sales", type number}, {"Date", type datetime}}),
- #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}, {"details.qty", Int64.Type}}),
- #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"details.net_sales", "Gross Sales Value"}}),
- #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Datetime", type datetime}}),
- #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "type", each if [details.qty] < 0 then "RETURN" else "SALE"),
- #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"details.type"}),
- #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"type", "details.type"}})
- in
- #"Renamed Columns1"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement