Power Query - Create Rows Interval Dates (TO - FROM )

 let

    Source = DIM_SAP_Centro_CustoTXT,

    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"DATEFROM", type date}, {"DATETO", type date}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Dates([DATEFROM], Number.From([DATETO]-[DATEFROM])+1, #duration(1, 0, 0, 0))),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each Text.StartsWith([Custom], "01")),

    #"Added Custom Column" = Table.AddColumn(#"Filtered Rows", "AnoMes", each Text.Combine({Date.ToText(Date.From([Custom]), "yyyy"), Date.ToText(Date.From([Custom]), "MM")}), type text),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom Column",{"AnoMes", "COSTCENTER", "TXTSH", "DATETO", "DATEFROM"})

in

    #"Removed Other Columns"

Power Query or SQL - CALCULATE DAY BETWEEN DATES

 Power Query:

Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([dt_Aberto] - [dt_Previsao]), Int64.Type)

SQL: 

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Tabular Editor Advanced = Search Filter sources

 

:Expression.ToUpper().Contains("APO")

:Name.EndsWith("Apo")

:Name.ToUpper().EndsWith("KEY")

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