Advertisement
Guest User

Untitled

a guest
May 24th, 2019
135
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.43 KB | None | 0 0
  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"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement