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 |
|
|
|
|
|
|
|
|
|
|
|
|
No comments:
Post a Comment