Power Query & SQL - Grouping Max Value ( Group by Group)

 script Power Query:

let

Source = Table.Combine({FAT_SAP_Lista_3, FAT_SAP_Lista_4}),

Step1 = Table.Group(Source, {"CUSTOMER"}, {{"MaxDate", each List.Max([DATEFROM]), type datetime}}),

Step2 = Table.Join(Source, {"CUSTOMER", "DATEFROM"}, Step1, {"CUSTOMER", "MaxDate"}),

Step3 = Table.SelectColumns(Step2,{"Source", "SHIP_COND", "KNVAL", "DATEFROM", "DATETO", "/BIC/ZMATERIAL", "CUSTOMER", "KNART","SALES_GRP"})

in

Step3

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

script SQL New:

WITH Source AS ( SELECT * FROM ( SELECT * FROM FAT_SAP_Lista_3 UNION ALL SELECT * FROM FAT_SAP_Lista_4 ) AS combined ), Step1 AS ( SELECT CUSTOMER, MAX(DATEFROM) AS MaxDate FROM Source GROUP BY CUSTOMER ), Step2 AS ( SELECT s.* FROM Source s INNER JOIN Step1 ON s.CUSTOMER = Step1.CUSTOMER AND s.DATEFROM = Step1.MaxDate ), Step3 AS ( SELECT Source, SHIP_COND, KNVAL, DATEFROM, DATETO, /BIC/ZMATERIAL, CUSTOMER, KNART, SALES_GRP FROM Step2 ) SELECT * FROM Step3;

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

script SQL Old:

???

Tabular Editor Advanced - Convert Measures Filters (Medidas em Filtros)

 

Install Tabular Editor:

https://tabulareditor.com/

https://www.sqlbi.com/tools/tabular-editor/

https://github.com/TabularEditor/TabularEditor/releases/tag/2.24.1

1 - Go to Table, create New calculation Group
2 - Rename "Name"

3 - Create New Calculations Items (...)

4 - Config each New Calculation Item (Print)

5 - Save

Power Query - Valida Preenchimento em Colunas

 = Table.TransformColumns(#"Added Custom1", {

{ "1", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "2", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "3", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "4", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "5", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "6", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "7", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "8", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "9", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "10", each if _ <> "" then 1 else 0, Int64.Type},

{ "11", each if _ <> "" then 1 else 0, Int64.Type},

{ "12", each if _ <> "" then 1 else 0, Int64.Type},

{ "13", each if _ <> "" then 1 else 0, Int64.Type},

{ "14", each if _ <> "" then 1 else 0, Int64.Type},

{ "15", each if _ <> "" then 1 else 0, Int64.Type},

{ "16", each if _ <> "" then 1 else 0, Int64.Type},

{ "17", each if _ <> "" then 1 else 0, Int64.Type},

{ "18", each if _ <> "" then 1 else 0, Int64.Type},

{ "19", each if _ <> "" then 1 else 0, Int64.Type},

{ "20", each if _ <> "" then 1 else 0, Int64.Type},

{ "21", each if _ <> "" then 1 else 0, Int64.Type},

{ "22", each if _ <> "" then 1 else 0, Int64.Type},

{ "23", each if _ <> "" then 1 else 0, Int64.Type},

{ "24", each if _ <> "" then 1 else 0, Int64.Type},

{ "25", each if _ <> "" then 1 else 0, Int64.Type},

{ "26", each if _ <> "" then 1 else 0, Int64.Type},

{ "27", each if _ <> "" then 1 else 0, Int64.Type},

{ "28", each if _ <> "" then 1 else 0, Int64.Type},

{ "29", each if _ <> "" then 1 else 0, Int64.Type},

{ "30", each if _ <> "" then 1 else 0, Int64.Type},

{ "31", each if _ <> "" then 1 else 0, Int64.Type},

{ "32", each if _ <> "" then 1 else 0, Int64.Type},

{ "33", each if _ <> "" then 1 else 0, Int64.Type},

{ "34", each if _ <> "" then 1 else 0, Int64.Type},

{ "35", each if _ <> "" then 1 else 0, Int64.Type},

{ "36", each if _ <> "" then 1 else 0, Int64.Type},

{ "37", each if _ <> "" then 1 else 0, Int64.Type},

{ "38", each if _ <> "" then 1 else 0, Int64.Type},

{ "39", each if _ <> "" then 1 else 0, Int64.Type},

{ "40", each if _ <> "" then 1 else 0, Int64.Type},

{ "41", each if _ <> "" then 1 else 0, Int64.Type},

{ "42", each if _ <> "" then 1 else 0, Int64.Type},

{ "43", each if _ <> "" then 1 else 0, Int64.Type},

{ "44", each if _ <> "" then 1 else 0, Int64.Type},

{ "45", each if _ <> "" then 1 else 0, Int64.Type},

{ "46", each if _ <> "" then 1 else 0, Int64.Type},

{ "47", each if _ <> "" then 1 else 0, Int64.Type},

{ "48", each if _ <> "" then 1 else 0, Int64.Type},

{ "49", each if _ <> "" then 1 else 0, Int64.Type},

{ "50", each if _ <> "" then 1 else 0, Int64.Type},

{ "51", each if _ <> "" then 1 else 0, Int64.Type},

{ "52", each if _ <> "" then 1 else 0, Int64.Type}

    })

DAX - Sample RLS

After create rules:

https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

dataset_RLS:

[Email] =  userprincipalname() 

or

[Email] =  USERNAME() 

Por exemplo, se o nome de usuário completo de um usuário for "jane.doe@contoso.com", a função USERNAME() retornaria apenas "jane.doe", enquanto a função USERPRINCIPALNAME() retornaria "jane.doe@contoso.com".

Em resumo, a função USERNAME() é útil quando você precisa apenas do nome de usuário sem o domínio, enquanto a função USERPRINCIPALNAME() é útil quando você precisa do identificador exclusivo do usuário.

DIM_Reunioes_Plan:

[SME] = LOOKUPVALUE(dataset_RLS[SME],dataset_RLS[Email],userprincipalname())

Power Query - Read DB Access and accdb files

 let

    Source = 

    //Access.Database(File.Contents("C:\Users\biprio\PRIO ENERGY SA(1)\W Analytics - BigData\datasets\SPANCOP\APOfiles\prio-truck prg.accdb"), [CreateNavigationProperties=true])

    OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\temp\FILE.accdb"";Jet OLEDB:Database Password="""";", [Query="SELECT * FROM TABLE "]),

    #"Removed Columns" = Table.RemoveColumns(Source,{"CDESCR"}),

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [NIF] <> null and [NIF] <> "")

in

    #"Filtered Rows"

PBIX - DAX - Combine Hibrid Years Recursive

 

Configuração via Power Query:

-----------------------------------------------------------

1 - Criar Parameter Anos

"2022,2023" meta [IsParameterQuery=true, List={"Diario", "2018", "2019", "2020", "2018,2019,2020", "2021"}, DefaultValue="Diario", Type="Text", IsParameterQueryRequired=true]

-----------------------------------------------------------

2 - Criar Função que fará chamada recursiva

let

    Source = (ano) => 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="786887ff-e45d-4d89-b874-b99d907cd9c3"]}[Data],

        Dataflow = Workspace{[dataflowName=Text.Combine({"Prio_BW_DF_Vendas_", ano})]}[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"}),

        Final_Table = try #"Expanded Data" otherwise EmptyTable

        

    in 

        Final_Table

in

    Source

-----------------------------------------------------------

3 - Criar TABLE:

let

    Parameters = Text.Split(Anos, ","),

    ParameterTable = Table.FromList(Parameters, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(ParameterTable,{{"Column1", "Ano"}}),

    TableList = Table.AddColumn(#"Renamed Columns", "Tables", each Get_FAT_Vendas([Ano])),

    CombinedTables = Table.Combine(TableList[Tables])

in

    CombinedTables

-----------------------------------------------------------

DAX - Comparação de informações entre 2 tabelas (PROCV)

.Vlr_PF_Diff =

CALCULATE ( SUMX ( Table01, Table01[Valor_Compara01] - CALCULATE ( SUMX ( RELATEDTABLE ( Table02 ), (Table02[Valor_Compara02] )), FILTER ( Table02 , AND(Table02[Var_A] = Table01[Var_A], AND(Table02[Var_B] = Table01[Var_B] , Table02[Var_C] = Table01[Var_C] )) ) ) ) )