Thursday, May 5, 2022

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 


- No comments:

Friday, April 22, 2022

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
- No comments:

Wednesday, April 6, 2022

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()
    )
- No comments:

Tuesday, March 22, 2022

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"

- No comments:

Wednesday, February 2, 2022

Power Query - dCalendar with ISOWeek

 fnc_ISOWeek:

/*
based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>

M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
`weeknum(x, 21)` doesn't give the correct ISO Week-Year.

homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>
*/

let
    getISO8601Week = (someDate as date) =>
        let
            getDayOfWeek = (d as date) =>
                let
                    result = 1 + Date.DayOfWeek(d, Day.Monday)
                in
                    result,
            getNaiveWeek = (inDate as date) =>
                let
                    // monday = 1, sunday = 7
                    weekday = getDayOfWeek(inDate),
                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
                    ordinal = Date.DayOfYear(inDate),
                    naiveWeek = Number.RoundDown(
                        (ordinal - weekday + 10) / 7
                    )
                in
                    naiveWeek,
            thisYear = Date.Year(someDate),
            priorYear = thisYear - 1,
            nwn = getNaiveWeek(someDate),
            lastWeekOfPriorYear =
                getNaiveWeek(#date(priorYear, 12, 28)),
            // http://stackoverflow.com/a/34092382/2014893
            lastWeekOfThisYear =
                getNaiveWeek(#date(thisYear, 12, 28)),
            weekYear =
                if
                    nwn < 1
                then
                    priorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        thisYear + 1
                    else
                        thisYear,
            weekNumber =
                if
                    nwn < 1
                then
                    lastWeekOfPriorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        1
                    else
                        nwn,
            week_dateString =
                Text.PadStart(
                    Text.From(
                        Number.RoundDown(weekNumber)
                    ),
                    2,
                    "0"
                )
        in
            Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate))
in
    getISO8601Week

==================
dCalendar:
let
   Source = List.Dates,
//   Step01 = Source(#date(2019, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2019,1,1)), #duration(1, 0, 0, 0)),
   Step01 = Source(#date(2016, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2016,1,1)), #duration(1, 0, 0, 0)),

   Step02 = Table.FromList(Step01, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   Step03 = Table.AddIndexColumn(Step02, "Index", 1, 1),
   Step04 = Table.RenameColumns(Step03,{{"Column1", "Date"}}),
   Step05 = Table.AddColumn(Step04, "Year", each Date.Year([Date])),
   Step06 = Table.AddColumn(Step05, "Month Number", each Number.ToText(Date.Month([Date]),"00")),
   Step07 = Table.AddColumn(Step06, "Day", each Number.ToText(Date.Day([Date]),"00")),
   Step08 = Table.AddColumn(Step07, "Day Name", each Date.ToText([Date],"ddd")),
   Step09 = Table.AddColumn(Step08, "Month Name", each Date.ToText([Date],"MMM")),
   Step10 = Table.AddColumn(Step09, "Quarter Number", each Date.QuarterOfYear([Date])),

   Step11 = Table.AddColumn(Step10, "Short Year", each Date.Year([Date])),
   Step12 = Table.TransformColumnTypes(Step11,{{"Short Year", type text}}),
   Step13 = Table.SplitColumn(Step12,"Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
   Step14 = Table.TransformColumnTypes(Step13,{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
   Step15 = Table.RemoveColumns(Step14,{"Short Year.1"}),
   Step16 = Table.RenameColumns(Step15,{{"Short Year.2", "Short Year"}}),

   Step17 = Table.AddColumn(Step16, "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
   Step18 = Table.AddColumn(Step17, "StartWeek", each Date.StartOfWeek([Date])),
   Step19 = Table.AddColumn(Step18, "YYYYMM", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00")),

   Step20n = Table.AddColumn(Step19, "YYYYWeekYear", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.WeekOfYear([Date], Day.Saturday),"00")),

   Step20 = Table.AddColumn(Step20n, "WeekDay", each Number.ToText(Date.DayOfWeek([Date], Day.Saturday),"00")),
   Step21 = Table.AddColumn(Step20, "YYYYMMDD", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),
   Step22 = Table.AddColumn(Step21, "MMDD", each  Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),
   Step23 = Table.TransformColumnTypes(Step22,{{"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}}),
   Step24 = Table.SelectRows(Step23, each true),
   Step25 = Table.Sort(Step24,{{"YYYYMMDD", Order.Descending}}),
   Step26 = Table.AddColumn(Step25, "WeekName", each Number.ToText([WeekDay]) & " - " & [Day Name]),
   Step27 = Table.AddColumn(Step26, "QtdDias", each DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - [Date]),
   Step28 = Table.AddColumn(Step27, "WeekYear", each Date.WeekOfYear([Date])),
   Step29 = Table.TransformColumnTypes(Step28,{{"QtdDias", Int64.Type}, {"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}, {"MMDD", Int64.Type}, {"WeekName", type text}, {"WeekYear", Int64.Type}}),
    Step30 = Table.AddColumn(Step29, "Zodiac", each if ([MMDD] >= 0121 and [MMDD] <= 0218) then "Z01" else
if ([MMDD] >= 0219 and [MMDD] <= 0320) then "Z02" else
if ([MMDD] >= 0321 and [MMDD] <= 0420) then "Z03" else 
if ([MMDD] >= 0421 and [MMDD] <= 0521) then "Z04" else 
if ([MMDD] >= 0522 and [MMDD] <= 0621) then "Z05" else
if ([MMDD] >= 0622 and [MMDD] <= 0722) then "Z06" else 
if ([MMDD] >= 0723 and [MMDD] <= 0823) then "Z07" else
if ([MMDD] >= 0824 and [MMDD] <= 0923) then "Z08" else
if ([MMDD] >= 0924 and [MMDD] <= 1023) then "Z09" else
if ([MMDD] >= 1024 and [MMDD] <= 1122) then "Z10" else
if ([MMDD] >= 1123 and [MMDD] <= 1221) then "Z11" else
if ([MMDD] >= 1222 or [MMDD] <= 0120) then "Z12" else "???"),
    #"Invoked Custom Function" = Table.AddColumn(Step30, "ISOWeek", each fnc_ISOWeek([Date]))
in
    #"Invoked Custom Function"

- No comments:
Subscribe to: Posts (Atom)

SQL - Query Analytics Basic

  SELECT Nome_Produto, ROUND(MIN(Valor_Venda), 2) AS Valor_Minimo, ROUND(MAX(Valor_Venda), 2) AS Valor_Maximo, ROUND(AV...