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

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