SHARE
TWEET

Untitled

a guest May 24th, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. let
  2.     // Filters
  3.     current_date = DateTime.Date(DateTime.LocalNow()),
  4.     end_date = Date.ToText(current_date,"yyyy-MM-dd") & " 23:59:59.000",
  5.     initial_date = Date.AddMonths(current_date,-2),
  6.     start_date = Date.ToText(initial_date,"yyyy-MM-dd"),
  7.     filter = "origin="&Origin&"&start_date="&start_date&"&end_date="&end_date,
  8.  
  9.     //Connection to service
  10.     GetTransactions = Web.Contents(Server, [ManualStatusHandling={204}, Headers=[Authorization="Bearer "&Token, ContentType="application/json"],   RelativePath="/transaction?"&filter]),
  11.     GetMetadata = Value.Metadata(GetTransactions),
  12.     GetResponseStatus = GetMetadata[Response.Status],
  13.     Output = if GetResponseStatus = 204 then "No Data" else GetTransactions,
  14.    
  15.     //Transformations
  16.     GetTransactionsDocument = Json.Document(Output),
  17.     #"Converted to Table" = Table.FromList(GetTransactionsDocument , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  18.     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"code", "status", "date", "location", "details", "id"}, {"code", "status", "date", "location", "details", "id"}),
  19.     #"Expanded details" = Table.ExpandListColumn(#"Expanded Column1", "details"),
  20.     #"Expanded details1" = Table.ExpandRecordColumn(#"Expanded details", "details", {"type", "product", "hr", "qty", "net_sales"}, {"details.type", "details.product", "details.hr", "details.qty", "details.net_sales"}),
  21.     #"Duplicated Column" = Table.DuplicateColumn(#"Expanded details1", "date", "date - Copy"),
  22.     #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"date", "Datetime"}, {"date - Copy", "Date"}}),
  23.     #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"details.net_sales", type number}, {"Date", type datetime}}),
  24.     #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}, {"details.qty", Int64.Type}}),
  25.     #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"details.net_sales", "Gross Sales Value"}}),
  26.     #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Datetime", type datetime}}),
  27.     #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "type", each if [details.qty] < 0 then "RETURN" else "SALE"),
  28.     #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"details.type"}),
  29.     #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"type", "details.type"}})
  30. in
  31.     #"Renamed Columns1"
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top