API tools faq
paste
Guest User

Untitled

a guest
Oct 1st, 2018
413
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 26.26 KB | None | 0 0
raw download clone embed print report
  1. let
  2. GetResults = (z as text, x as number) =>
  3. let
  4. S = Json.Document(Web.Contents(ServiceRootURL & "/leads", [Headers=[Prefer="odata.include-annotations=*"],Query=[fetchXml="
  5. <fetch page=""" & Text.From(x) & """ paging-cookie=""" & z & """>
  6. <entity name=""lead""> <all-attributes />
  7.  
  8. </entity>
  9. </fetch>"]])),
  10. P = try Xml.Document(S[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null,
  11. R = if P <> null
  12. then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", "&gt;"), "<", "&lt;"), """", "&quot;"), x + 1)})
  13. else S[value]
  14. in
  15. R,
  16. ResultsList = GetResults("", 1),
  17. ResultsTable = if List.IsEmpty(ResultsList)
  18. then #table(
  19. type table[ #"Lead ID"= text,
  20. #"Created On"= datetimezone,
  21. #"Owner (ownerid)"= text,
  22. #"Owner (Type)"= text,
  23. #"Owner"= text,
  24. #"Country of sale (ccs_country_of_sale)"= text,
  25. #"Country of sale"= text,
  26. #"Line of Business (ccs_lineofbusiness)"= text,
  27. #"Line of Business"= text,
  28. #"Account (ccs_account)"= text,
  29. #"Account"= text,
  30. #"Products (ccs_products)"= text,
  31. #"Products"= text,
  32. #"Annual Recurring Revenue"= number,
  33. #"ARR in USD"= text,
  34. #"ARR in USD(Formatted Value)"= text,
  35. #"Target Margin% (ccs_targetmargin)"= text,
  36. #"Target Margin%"= text,
  37. #"Stage Name"= text,
  38. #"Closed Status (ccs_closedstatus)"= text,
  39. #"Closed Status"= text,
  40. #"Lead Source (leadsourcecode)"= text,
  41. #"Lead Source"= text,
  42. #"Head Count"= number,
  43. #"Expected Closure Date"= date,
  44. #"Target Go Live Quarter (ccs_target_go_live_quarter)"= text,
  45. #"Target Go Live Quarter"= text,
  46. #"Target Go Live Period (ccs_target_go_live_period)"= text,
  47. #"Target Go Live Period"= text,
  48. #"Modified By (modifiedby)"= text,
  49. #"Modified By"= text,
  50. #"Modified On"= datetimezone,
  51. #"Latest Update"= text,
  52. #"Agreement Closure Date"= date,
  53. #"Implementation Fee Charged (ccs_implementationfee)"= text,
  54. #"Implementation Fee Charged"= text,
  55. #"Implementation Fee Applicability (ccs_implementationfeeapplicability)"= text,
  56. #"Implementation Fee Applicability"= text,
  57. #"Implementation Fee Currency (ccs_implementationfeecurrency)"= text,
  58. #"Implementation Fee Currency"= text,
  59. #"Implementation Fee"= text,
  60. #"Implementation Fee USD"= text,
  61. #"Weightage %"= number,
  62. #"Weighted Avg"= number,
  63. #"Closed Won Date"= date,
  64. #"Company Name"= text,
  65. #"leadid"= text ],{})
  66. else #"Converted to Table",
  67. #"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  68. #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
  69. {
  70. "ccs_gid",
  71. "createdon",
  72. "_ownerid_value",
  73. "[email protected]",
  74. "[email protected]",
  75. "_ccs_country_of_sale_value",
  76. "_ccs_country_of_sale_value@OData.Community.Display.V1.FormattedValue",
  77. "ccs_lineofbusiness",
  78. "[email protected]",
  79. "_ccs_account_value",
  80. "[email protected]",
  81. "_ccs_products_value",
  82. "[email protected]",
  83. "ccs_annualrecurringrevenue",
  84. "ccs_arrinusd",
  85. "[email protected]",
  86. "ccs_targetmargin",
  87. "[email protected]",
  88. "ccs_reportingstage",
  89. "ccs_closedstatus",
  90. "[email protected]",
  91. "leadsourcecode",
  92. "[email protected]",
  93. "ccs_headcount",
  94. "ccs_expected_closure_date",
  95. "ccs_target_go_live_quarter",
  96. "ccs_target_go_live_quarter@OData.Community.Display.V1.FormattedValue",
  97. "ccs_target_go_live_period",
  98. "ccs_target_go_live_period@OData.Community.Display.V1.FormattedValue",
  99. "_modifiedby_value",
  100. "[email protected]",
  101. "modifiedon",
  102. "ccs_latest_update",
  103. "ccs_agreementclosuredate",
  104. "ccs_implementationfee",
  105. "[email protected]",
  106. "ccs_implementationfeeapplicability",
  107. "ccs_implementationfeeapplicability@OData.Community.Display.V1.FormattedValue",
  108. "_ccs_implementationfeecurrency_value",
  109. "_ccs_implementationfeecurrency_value@OData.Community.Display.V1.FormattedValue",
  110. "ccs_implementation_fee",
  111. "ccs_implementationfeeusd",
  112. "ccs_weightage",
  113. "ccs_weightedaverage",
  114. "ccs_wondate",
  115. "companyname",
  116. "leadid"
  117. },
  118.  
  119. {
  120. "ccs_gid",
  121. "createdon",
  122. "_ownerid_value",
  123. "[email protected]",
  124. "[email protected]",
  125. "_ccs_country_of_sale_value",
  126. "_ccs_country_of_sale_value@OData.Community.Display.V1.FormattedValue",
  127. "ccs_lineofbusiness",
  128. "[email protected]",
  129. "_ccs_account_value",
  130. "[email protected]",
  131. "_ccs_products_value",
  132. "[email protected]",
  133. "ccs_annualrecurringrevenue",
  134. "ccs_arrinusd",
  135. "[email protected]",
  136. "ccs_targetmargin",
  137. "[email protected]",
  138. "ccs_reportingstage",
  139. "ccs_closedstatus",
  140. "[email protected]",
  141. "leadsourcecode",
  142. "[email protected]",
  143. "ccs_headcount",
  144. "ccs_expected_closure_date",
  145. "ccs_target_go_live_quarter",
  146. "ccs_target_go_live_quarter@OData.Community.Display.V1.FormattedValue",
  147. "ccs_target_go_live_period",
  148. "ccs_target_go_live_period@OData.Community.Display.V1.FormattedValue",
  149. "_modifiedby_value",
  150. "[email protected]",
  151. "modifiedon",
  152. "ccs_latest_update",
  153. "ccs_agreementclosuredate",
  154. "ccs_implementationfee",
  155. "[email protected]",
  156. "ccs_implementationfeeapplicability",
  157. "ccs_implementationfeeapplicability@OData.Community.Display.V1.FormattedValue",
  158. "_ccs_implementationfeecurrency_value",
  159. "_ccs_implementationfeecurrency_value@OData.Community.Display.V1.FormattedValue",
  160. "ccs_implementation_fee",
  161. "ccs_implementationfeeusd",
  162. "ccs_weightage",
  163. "ccs_weightedaverage",
  164. "ccs_wondate",
  165. "companyname",
  166. "leadid"
  167. }),
  168. #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
  169. {
  170. {"ccs_gid", "Lead ID"},
  171. {"createdon", "Created On"},
  172. {"_ownerid_value", "Owner (ownerid)"},
  173. {"[email protected]", "Owner (Type)"},
  174. {"[email protected]", "Owner"},
  175. {"_ccs_country_of_sale_value", "Country of sale (ccs_country_of_sale)"},
  176. {"_ccs_country_of_sale_value@OData.Community.Display.V1.FormattedValue", "Country of sale"},
  177. {"ccs_lineofbusiness", "Line of Business (ccs_lineofbusiness)"},
  178. {"[email protected]", "Line of Business"},
  179. {"_ccs_account_value", "Account (ccs_account)"},
  180. {"[email protected]", "Account"},
  181. {"_ccs_products_value", "Products (ccs_products)"},
  182. {"[email protected]", "Products"},
  183. {"ccs_annualrecurringrevenue", "Annual Recurring Revenue"},
  184. {"ccs_arrinusd", "ARR in USD"},
  185. {"[email protected]", "ARR in USD(Formatted Value)"},
  186. {"ccs_targetmargin", "Target Margin% (ccs_targetmargin)"},
  187. {"[email protected]", "Target Margin%"},
  188. {"ccs_reportingstage", "Stage Name"},
  189. {"ccs_closedstatus", "Closed Status (ccs_closedstatus)"},
  190. {"[email protected]", "Closed Status"},
  191. {"leadsourcecode", "Lead Source (leadsourcecode)"},
  192. {"[email protected]", "Lead Source"},
  193. {"ccs_headcount", "Head Count"},
  194. {"ccs_expected_closure_date", "Expected Closure Date"},
  195. {"ccs_target_go_live_quarter", "Target Go Live Quarter (ccs_target_go_live_quarter)"},
  196. {"ccs_target_go_live_quarter@OData.Community.Display.V1.FormattedValue", "Target Go Live Quarter"},
  197. {"ccs_target_go_live_period", "Target Go Live Period (ccs_target_go_live_period)"},
  198. {"ccs_target_go_live_period@OData.Community.Display.V1.FormattedValue", "Target Go Live Period"},
  199. {"_modifiedby_value", "Modified By (modifiedby)"},
  200. {"[email protected]", "Modified By"},
  201. {"modifiedon", "Modified On"},
  202. {"ccs_latest_update", "Latest Update"},
  203. {"ccs_agreementclosuredate", "Agreement Closure Date"},
  204. {"ccs_implementationfee", "Implementation Fee Charged (ccs_implementationfee)"},
  205. {"[email protected]", "Implementation Fee Charged"},
  206. {"ccs_implementationfeeapplicability", "Implementation Fee Applicability (ccs_implementationfeeapplicability)"},
  207. {"ccs_implementationfeeapplicability@OData.Community.Display.V1.FormattedValue", "Implementation Fee Applicability"},
  208. {"_ccs_implementationfeecurrency_value", "Implementation Fee Currency (ccs_implementationfeecurrency)"},
  209. {"_ccs_implementationfeecurrency_value@OData.Community.Display.V1.FormattedValue", "Implementation Fee Currency"},
  210. {"ccs_implementation_fee", "Implementation Fee"},
  211. {"ccs_implementationfeeusd", "Implementation Fee USD"},
  212. {"ccs_weightage", "Weightage %"},
  213. {"ccs_weightedaverage", "Weighted Avg"},
  214. {"ccs_wondate", "Closed Won Date"},
  215. {"companyname", "Company Name"},
  216. {"leadid", "leadid"}
  217. }),
  218. #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
  219. {
  220. {"Lead ID", type text},
  221. {"Created On", type datetime},
  222. {"Owner (ownerid)", type text},
  223. {"Owner (Type)", type text},
  224. {"Owner", type text},
  225. {"Country of sale (ccs_country_of_sale)", type text},
  226. {"Country of sale", type text},
  227. {"Line of Business (ccs_lineofbusiness)", type text},
  228. {"Line of Business", type text},
  229. {"Account (ccs_account)", type text},
  230. {"Account", type text},
  231. {"Products (ccs_products)", type text},
  232. {"Products", type text},
  233. {"Annual Recurring Revenue", type number},
  234. {"ARR in USD", type number},
  235. {"ARR in USD(Formatted Value)", type number},
  236. {"Target Margin% (ccs_targetmargin)", type text},
  237. {"Target Margin%", type text},
  238. {"Stage Name", type text},
  239. {"Closed Status (ccs_closedstatus)", type text},
  240. {"Closed Status", type text},
  241. {"Lead Source (leadsourcecode)", type text},
  242. {"Lead Source", type text},
  243. {"Head Count", type number},
  244. {"Expected Closure Date", type datetime},
  245. {"Target Go Live Quarter (ccs_target_go_live_quarter)", type text},
  246. {"Target Go Live Quarter", type text},
  247. {"Target Go Live Period (ccs_target_go_live_period)", type text},
  248. {"Target Go Live Period", type text},
  249. {"Modified By (modifiedby)", type text},
  250. {"Modified By", type text},
  251. {"Modified On", type datetime},
  252. {"Latest Update", type text},
  253. {"Agreement Closure Date", type datetime},
  254. {"Implementation Fee Charged (ccs_implementationfee)", type text},
  255. {"Implementation Fee Charged", type text},
  256. {"Implementation Fee Applicability (ccs_implementationfeeapplicability)", type text},
  257. {"Implementation Fee Applicability", type text},
  258. {"Implementation Fee Currency (ccs_implementationfeecurrency)", type text},
  259. {"Implementation Fee Currency", type text},
  260. {"Implementation Fee", type text},
  261. {"Implementation Fee USD", type text},
  262. {"Weightage %", type number},
  263. {"Weighted Avg", type number},
  264. {"Closed Won Date", type date},
  265. {"Company Name", type text},
  266. {"leadid", type text}
  267. })
  268. ,
  269. #"Added Link" = Table.AddColumn(#"Changed Type", "Link", each Dyn365CEBaseURL & "/main.aspx?etn=lead&pagetype=entityrecord&id=%7b"& [leadid]&"%7d"),
  270. #"Result" = if List.IsEmpty(ResultsList)
  271. then ResultsTable
  272. else #"Added Link",
  273. #"Removed Columns" = Table.RemoveColumns(Result,{"Owner (ownerid)", "Owner (Type)", "Country of sale (ccs_country_of_sale)", "Line of Business (ccs_lineofbusiness)", "Account (ccs_account)", "Products (ccs_products)", "Annual Recurring Revenue", "Target Margin% (ccs_targetmargin)", "ARR in USD(Formatted Value)", "Closed Status (ccs_closedstatus)", "Lead Source (leadsourcecode)", "Target Go Live Quarter (ccs_target_go_live_quarter)", "Target Go Live Period (ccs_target_go_live_period)", "Modified By (modifiedby)", "Implementation Fee Charged (ccs_implementationfee)", "Implementation Fee Applicability (ccs_implementationfeeapplicability)", "Implementation Fee Currency (ccs_implementationfeecurrency)", "Closed Won Date", "leadid"}),
  274. #"Added ARR Cluster" = Table.AddColumn(#"Removed Columns", "Arr Cluster", each if [ARR in USD] <= 100000 then "Silver" else if [ARR in USD] <= 200000 then "Gold" else if [ARR in USD] <= 300000 then "Platinum" else if [ARR in USD] > 300000 then "Diamond" else null),
  275. #"Replaced Errors(ARR Cluster)" = Table.ReplaceErrorValues(#"Added ARR Cluster", {{"Arr Cluster", "Silver"}}),
  276. #"Replaced null to Open(Closed Status)" = Table.ReplaceValue(#"Replaced Errors(ARR Cluster)",null,"Open",Replacer.ReplaceValue,{"Closed Status"}),
  277. #"Added ARR Cluster Sort" = Table.AddColumn(#"Replaced null to Open(Closed Status)", "ARRClusterSort", each if [Arr Cluster] = "Silver" then 1 else if [Arr Cluster] = "Gold" then 2 else if [Arr Cluster] = "Platinum" then 3 else 4),
  278. #"Filtered Rows" = Table.SelectRows(#"Added ARR Cluster Sort", each ([Stage Name] = "6-Closed Contracted") and ([Closed Status] = "Won")),
  279. #"Inserted ACD Quarter" = Table.AddColumn(#"Filtered Rows", "ACD Quarter", each Date.QuarterOfYear([Agreement Closure Date]), Int64.Type),
  280. #"Inserted ACD Month" = Table.AddColumn(#"Inserted ACD Quarter", "ACD Month", each Date.Month([Agreement Closure Date]), Int64.Type),
  281. #"Inserted ACD Year" = Table.AddColumn(#"Inserted ACD Month", "ACD Year", each Date.Year([Agreement Closure Date]), Int64.Type),
  282. #"Added ACD Quarter Name" = Table.AddColumn(#"Inserted ACD Year", "ACD Quarter Name", each if [ACD Quarter] = 1 then "Q1" else if [ACD Quarter] = 2 then "Q2" else if [ACD Quarter] = 3 then "Q3" else if [ACD Quarter] = 4 then "Q4" else null),
  283. #"Added ACD Month Name" = Table.AddColumn(#"Added ACD Quarter Name", "ACD Month Name", each if [ACD Month] = 1 then "January" else if [ACD Month] = 2 then "February" else if [ACD Month] = 3 then "March" else if [ACD Month] = 4 then "April" else if [ACD Month] = 5 then "May" else if [ACD Month] = 6 then "June" else if [ACD Month] = 7 then "July" else if [ACD Month] = 8 then "August" else if [ACD Month] = 9 then "September" else if [ACD Month] = 10 then "October" else if [ACD Month] = 11 then "November" else if [ACD Quarter] = 12 then "December" else null),
  284. #"Grouped Rows" = Table.Group(#"Added ACD Month Name", {"Country of sale", "Line of Business", "ACD Quarter Name", "ACD Month Name", "ACD Year"}, {{"ARR in USD", each List.Sum([ARR in USD]), type number}}),
  285. #"Added Month Range" = Table.AddColumn(#"Grouped Rows", "Month Range", each if [ACD Quarter Name] = "Q1" then "Jan - Mar" else if [ACD Quarter Name] = "Q2" then "Apr - Jun" else if [ACD Quarter Name] = "Q3" then "Jul - Sep" else if [ACD Quarter Name] = "Q4" then "Oct - Dec" else null),
  286. #"Uppercased Text" = Table.TransformColumns(#"Added Month Range",{{"Country of sale", Text.Upper, type text}, {"Line of Business", Text.Upper, type text}, {"ACD Month Name", Text.Upper, type text}, {"Month Range", Text.Upper, type text}}),
  287. #"Merged Queries" = Table.NestedJoin(#"Uppercased Text",{"Country of sale", "Line of Business", "ACD Month Name", "ACD Quarter Name", "ACD Year"},#"Target Data_new",{"Country", "Line of Business", "Month", "Quarter", "Year"},"Target Data_new",JoinKind.FullOuter),
  288. #"Expanded Target Data_new" = Table.ExpandTableColumn(#"Merged Queries", "Target Data_new", {"Country", "Line of Business", "Q-Year", "Month", "Quarter", "Year", "Month Ranges", "Target"}, {"Target Data_new.Country", "Target Data_new.Line of Business", "Target Data_new.Q-Year", "Target Data_new.Month", "Target Data_new.Quarter", "Target Data_new.Year", "Target Data_new.Month Ranges", "Target Data_new.Target"})
  289. in
  290. #"Expanded Target Data_new"
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!