Python - SpeedTest with Power BI

 

Python Code:

DAX - RANK

.Rank_ERRADO =

IF( HASONEVALUE(RFM_SAP_FAT_Vendas_py[CustomerID]),

RANKX(ALL (RFM_SAP_FAT_Vendas_py[CustomerID]), RFM_SAP_FAT_Vendas_py[.RankSum]))

.Rank2_CERTO =

IF( HASONEVALUE(RFM_SAP_FAT_Vendas_py[CustomerID]),

RANKX(ALL (RFM_SAP_FAT_Vendas_py[CustomerID]), RFM_SAP_FAT_Vendas_py[.RankSum]))

.RankSum = SUM(RFM_SAP_FAT_Vendas_py[TotalQtd])

OBS:  

.Rank2_CERTO e .RankSum são Measures

.Rank_ERRADO é Column Calc

Power Query - API (API-Key and Basic) or Relative Path

 let

  P_Path = "https://???"

Source = Json.Document(Web.Contents(P_Path, [Headers=[#"API-Key"="??????", Authorization="Basic ???????"]])),

  #"Converted to Table" = Table.FromRecords({Source}),

in

#"Converted to Table"

===============================================

With Relative Path:

===============================================

//fnc_URL 

let 

  Query = () => 

let 

  Source = "https://xxxxxxxxxxxxxxxxxxxxxxxxxx&offset=" 

in 

  Source 

in 

  Query 

   

===============================================

//fnc_Auth 

let 

  Query = () => 

let 

  Source = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" 

in 

  Source 

in 

  Query 

  

===============================================

//fnc_Pages 

let 

  fnc_Pages = () => 

let 

  result = Json.Document(Web.Contents("https://xxxxxxxxxxxoffset=0&limit=100", [Headers=[Authorization=fnc_Auth(), ContentType="application/json", Accept="application/json"]])), 

  totalItems = result[totalResults], 

  pageRange = {0..Number.RoundUp(totalItems / 100)-1}, 

  #"Converted to table" = Table.FromList(pageRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 

  #"Added custom" = Table.AddColumn(#"Converted to table", "Column2", each [Column1] * 100), 

  #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Column1"}) 

in 

  #"Removed columns" 

in 

  fnc_Pages 

 

  

===============================================

//fnc_EP 

let 

  fnc_EP = ()=> 

let 

  // PagS = 0, 

  // PagE = 100, 

  // Col = Table.FromList({0,100}, Splitter.SplitByNothing(), null,null, ExtraValues.Error), 

  Col = fnc_Pages(), 

  // #"Changed column type" = Table.TransformColumnTypes(Col, {{"Column1", type text}}), 

  // URL1 = Table.AddColumn(#"Changed column type", "URL1", each fnc_URL() & [Column1]), 

  // #"Removed columns" = Table.RemoveColumns(URL1, {"Column1"}), 

  #"Changed column type" = Table.TransformColumnTypes(Col, {{"Column2", type text}}), 

  URL1 = Table.AddColumn(#"Changed column type", "URL1", each fnc_URL() & [Column2]), 

  #"Removed columns" = Table.RemoveColumns(URL1, {"Column2"}), 

  #"Changed column type 1" = Table.TransformColumnTypes(#"Removed columns", {{"URL1", type text}}), 

  #"Extracted text after delimiter" = Table.TransformColumns(#"Changed column type 1", {{"URL1", each Text.AfterDelimiter(_, "https://xxxxxxxxxxxxxxxxxxxxx.com/", 0), type text}}) 

in 

  #"Extracted text after delimiter" 

in 

  fnc_EP 

  

===============================================

//Table Data 

let 

  Source = fnc_EP(), 

  // URL2 = Table.AddColumn(#"Changed column type", "URL2", each Params[BaseURL]&Params[EndPoint]&Params[Query]), 

  // #"Changed column type 1" = Table.TransformColumnTypes(URL2, {{"URL2", type text}}), 

  // JSON = Table.AddColumn(#"Changed column type 1", "DATA", each Json.Document(Web.Contents([URL1], [Headers=[Authorization=Params[Authorization], ContentType="application/json", Accept="application/json"]]))), 

  // JSON = Table.AddColumn(#"Changed column type 1", "DATA", each Json.Document(Web.Contents([URL2],[Headers=[Authorization=Params[Authorization], ContentType="application/json", Accept="application/json"]]))), 

  JSON = Table.AddColumn(Source, "DATA", each Json.Document(Web.Contents("https://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.com/",  

  [RelativePath = [URL1], Headers=[ContentType="application/json", Accept="application/json",Authorization=fnc_Auth() ]]))), 

in 

  JSON