Power BI - Motor de Alarmisticas via Visual Table (Python Azure DAX PBIX)

 Step by step para buscar dados de dentro de um Visual Table 

1. Criar PBIX e gerar a tabela com as informações minimas ( 

   E-mail 
1. Valor a Monitorar 
2. Valor Range In 
3. Valor Range out) 
2. Gerar query via PBIX 
3. Executar/Testar query via DaxStudio 
4. Ajustar query JSON para execução REST API  
1. Replace:  " por \"  
5. Testar query via REST API 

 

 

 

 

 

ID	Descrição
1
2
3
4	{    "queries": [      {        "query":   "  DEFINE    VAR __DS0Core =       SUMMARIZECOLUMNS(        ROLLUPADDISSUBTOTAL(          ROLLUPGROUP('DIM_Alarmes'[Email], 'DIM_Alarmes'[Status]), \"IsGrandTotalRowTotal\"        ),        \"v_Valores\", 'DIM_Alarmes'[.Valores],        \"v_qtdRegs\", 'DIM_Alarmes'[.qtdRegs]      )       VAR __DS0PrimaryWindowed =       TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'DIM_Alarmes'[Status], 0, 'DIM_Alarmes'[Email], 1)     EVALUATE    __DS0PrimaryWindowed     ORDER BY    [IsGrandTotalRowTotal] DESC, 'DIM_Alarmes'[Status] DESC, 'DIM_Alarmes'[Email]     "      }    ],    "serializerSettings": {      "includeNulls": true    }     }
5	https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group#code-try-0
6

 

 

 

 

 

Power Query - Loader Data Dynamic

 

Follow step by step dynamic execution by Year...

1 – Criar um parametro para utilização dos anos que se quer carregar
2 – Criar função Get_FAT_Vendas  que buscará os Dfs , Tables por Anos	let     Source = (P_Anos) => let              EmptyTable = #table({"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}, {}),           Source = PowerBI.Dataflows(null),         Workspace = Source{[workspaceId="34kj5k345k3j45j3j4k53k45j345k3jj34"]}[Data],         Dataflow = Workspace{[dataflowName=Text.Combine({"DF_Vendas_", P_Anos })]}[Data],           #"Removed Other Columns" = Table.SelectColumns(Dataflow,{"Data"}),         #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}, {"CALDAY", "DOC_CURRCY", "SALES_UNIT", "CUST_SALES", "ZMATERIAL", "DISTR_CHAN", "DIVISION", "SALESORG", "PLANT", "DOC_TYPE", "ZWS_PAYMD", "DOC_CATEG", "HH", "ZWS_SUBTP", "ZWS_SBPAY", ".NIF_Venda", "ZOPERADOR", "ZCARDNUM1", "GROSS_VAL", "TAX_VALUE", "CML_OR_QTY", "NET_VALUE"}),         #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"CALDAY", Int64.Type}, {"CUST_SALES", Int64.Type}, {"ZMATERIAL", Int64.Type}, {"ZCARDNUM1", type text}, {"DOC_CURRCY", type text}, {"SALES_UNIT", type text}, {"DISTR_CHAN", type text}, {"DIVISION", type text}, {"SALESORG", type text}, {"PLANT", type text},  {"DOC_TYPE", type text}, {"ZWS_PAYMD", type text}, {"DOC_CATEG", type text}, {"HH", type text}, {"ZWS_SUBTP", type text}, {"ZWS_SBPAY", type text}, {".NIF_Venda", type text}, {"ZOPERADOR", type text},  {"GROSS_VAL", type number}, {"TAX_VALUE", type number}, {"CML_OR_QTY", type number}, {"NET_VALUE", type number}}),         Final_Table = try #"Changed Type" otherwise EmptyTable             in         Final_Table in     Source
3 – Criar a DATASET do PBIX	let     Parameters = Text.Split(P_Anos, ","),     ParameterTable = Table.FromList(Parameters, Splitter.SplitByNothing(), null, null, ExtraValues.Error),     #"Renamed Columns" = Table.RenameColumns(ParameterTable,{{"Column1", " P_Anos "}}),     TableList = Table.AddColumn(#"Renamed Columns", "Tables", each Get_FAT_Vendas([P_Anos])),       CombinedTables = Table.Combine(TableList[Tables]) in     CombinedTables

DAX - Calculo de tempo , age, idade

 .Age = INT ( YEARFRAC ( [birthday].[Date], TODAY (), 1 ) )

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

 .Age = INT ( YEARFRAC ( [birthday].[Date], TODAY (), 1 ) )

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

.V_Dt_Encerramento =

if(TRIM([/BIC/ZDTENCERR]) = "" , "ERR Enc Null",

if(TRIM([/BIC/ZDTABER]) = "" , "Err Abt Null",

if([/BIC/ZDTENCERR] = "00000000", "ERR Enc ZERO",

if(AND(MID([/BIC/ZDTENCERR],"1","4") = "9999",MID([/BIC/ZDTABER],"1","4") = "9999"), "Err - Dup Abt & Fec",

if(AND(MID([/BIC/ZDTENCERR],"1","4") = "9999",MID([/BIC/ZDTABER],"1","4") <> "9999"), "OK - Aberto",

if(NOT OR(MID([/BIC/ZDTENCERR],"1","1") = "1",

          MID([/BIC/ZDTENCERR],"1","1") = "2"), "ERR DT",

"OK - Fechado"))))))

.qtdAnosP =

var dtA_year = INT(MID([/BIC/ZDTABER],"1","4"))

var dtA_month = INT(MID([/BIC/ZDTABER],"5","2"))

var dtA_day = (MID([/BIC/ZDTABER],"7","2"))

var dtA = DATE( (dtA_year) ,   (dtA_month) ,   (dtA_day))

RETURN

IF(DIM_SAP_Postos_New[.V_Dt_Encerramento] = "OK - Aberto",INT ( YEARFRAC ( dtA, TODAY (), 1 ) ))