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 

Python - Script Main.py call scripts.py

1 - ========================================

script1.py:

#!/usr/bin/env python3

print('Hello World!1')

script2.py:

#!/usr/bin/env python3

print('Hello World!2')

script_main.py:

#!/usr/bin/env python3

import subprocess

subprocess.call("./script1.py", shell=True)

subprocess.call("./script2.py", shell=True)

Permissions scripts:

chmod u+x script1.py

chmod u+x script2.py

chmod u+x script_main.py

Python line:

./script_main.py

2 - ========================================

script_main.py:

#!/usr/bin/env python
# coding: utf-8

import runpy

runpy.run_path(path_name='script1.py')
runpy.run_path(path_name='script2.py')

Python line:

./script_main.py

DAX - Measures L4L Like for Like (Compare Periods)

=============================Exemplo 00
Variação Percentual Ano-a-Ano = 

VAR AnoAtual = MAX('Tabela'[AnoColumn]) // Substitui pelo nome da tua coluna que guarda o ano

VAR AnoAnterior = AnoAtual - 1

VAR ValorAtual = 

    CALCULATE(

        SUM('Tabela'[ValorColumn]), // Substitui pelo nome da tua coluna que guarda o valor

        'Tabela'[AnoColumn] = AnoAtual

    )

VAR ValorAnterior = 

    CALCULATE(

        SUM('Tabela'[ValorColumn]),

        'Tabela'[AnoColumn] = AnoAnterior

    )

RETURN

IF(

    ValorAnterior <> 0,

    DIVIDE(ValorAtual - ValorAnterior, ValorAnterior)

)

=============================Exemplo 01

.qtdCarr = DISTINCTCOUNT(FAT_Careg[FK])

.CarrMesAtual =

CALCULATE(

[.qtdCarr],

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Year]=YEAR(TODAY())),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Month Number]=MONTH(TODAY())),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Date] <=

MAX(dCalendar_Carregamentos[Date]) //TODAY()

))

.CarrMesAnterior =

var VFilter = MAX(dCalendar_Carregamentos[Date]) //TODAY()

var VAno = YEAR(EOMONTH(VFilter,-1))

var VMes = MONTH(EOMONTH(VFilter,-1))

var VCheckDia = IF(DAY(VFilter) > DAY(EOMONTH(VFilter,-1)), DAY(EOMONTH(VFilter,-1)),DAY(VFilter))

var VDia = DATE(YEAR(EOMONTH(VFilter,-1)),MONTH(EOMONTH(VFilter,-1)),VCheckDia)

RETURN

CALCULATE(

[.qtdCarr],

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Year]=VAno),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Month Number]= VMes),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Date] <= VDia)

)

.CarrMesAnoAnterior =

var VFilter = Max(dCalendar_Carregamentos[Date])

var VAno = YEAR(VFilter)-1

var VMes = MONTH(VFilter)

var VDia = DATE(VAno,VMes,DAY(VFilter))

RETURN

CALCULATE(

[.qtdCarr],

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Year]=VAno),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Month Number]= VMes),

FILTER(dCalendar_Carregamentos,dCalendar_Carregamentos[Date] <= VDia)

)

=============================Exemplo 02

Measure_Actual_Days = M_Month ActualYear = TOTALMTD(SUM(FAT_Sales[CML_OR_QTY]),DIM_Calendar[Date]) / 1000

Measure_Actual_Days_Month =M_Month LastYear = CALCULATE([M_Month ActualYear], SAMEPERIODLASTYEAR(DIM_Calendar[Date]))

Measure_Actual_Days_YTD =M_YTD ActualYear = TOTALYTD(SUM(FAT_Sales[CML_OR_QTY]),DIM_Calendar[Date]) / 1000

Measure_Actual_Days_YTD_Year =M_YTD LastYear = CALCULATE([M_YTD ActualYear], SAMEPERIODLASTYEAR(DIM_Calendar[Date]))

M_Month Variance = -1 *(([M_Month LastYear] - [M_Month ActualYear]) / [M_Month LastYear])

M_YTD Variance = -1 *(([M_YTD LastYear] - [M_YTD ActualYear]) / [M_YTD LastYear])

=============================Exemplo 03

.QtdCurr =

CALCULATE(

    FAT_Vendas_RealTime[.QtdVendas],

    FILTER(

        FAT_Vendas_RealTime,

        DAY(FAT_Vendas_RealTime[date]) = DAY(TODAY())

    )

)

.QtdCurrLastDay =

CALCULATE(

    [.QtdVendas],

    FILTER(

        FAT_Vendas_RealTime,

        DAY(FAT_Vendas_RealTime[date]) = DAY(TODAY()) - 1

    )

)

.QtdCurrDiff = [.QtdCurr] - [.QtdCurrLastDay]

.QtdCurr% =

var perc = DIVIDE([.QtdCurrDiff],[.QtdCurrLastDay],0)

RETURN

if([.QtdCurr] = BLANK() , BLANK()  , perc)

.QtdCurrValid =

VAR Ultima_Hora = CALCULATE(MAX(FAT_Vendas_RealTime[HH]))

VAR Diferenca_Percentual = [.QtdCurr%]

RETURN

    IF(

        Ultima_Hora = HOUR(NOW())-1,

        IF(

            OR(Diferenca_Percentual = 0 , Diferenca_Percentual = BLANK()) ,

            "NOK",

            "OK"

        ),

        BLANK()

    )

DAX - Create Measure Folder (Organizando Medidas)

 

Para agrupar as medidas em Folders:

1. Ir até Modelo de Dados
2. General -> Properties
3. Selecionar as Medidas que deseja agrupar
4. Adicionar o nome da Folder no campo "Display Folder"