Ситуация следующая: есть один основной запрос, и три дополнительных, из которых я в основной запрос тяну данные с помощью Merged, после чего провожу манипуляции: фильтрация по одному или нескольким признакам, создание условных столбцов, замена значений в столбцах (некоторые - по условию), изменения формата данных, перестанока, переименование, удаление столбцов, сортировка. Основной запрос и один из трех дополнительных тянутся из CSV файлов, примерно от 90-220 тысяч строк каждый.
Вопрос: имеет ли смысл использовать в моем случае Table.Buffer, или нет?
Изучал тут, тут и тут. Понимаю смысл самой функции, но не могу понять, как её применить в моем случае: например, лучше будет сперва отmerge-ить все доп. запросы, закинуть таблицу в буфер, после чего проводить все манипуляции, либо сначала отфильтровать все, потом буфер, потом все остальное, либо вообще оставить всё как есть.
Код главного запроса, на всякий случай:
Скрытый текст
Код
let
Source = Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="PQSettings"]}[Content]{0}[Value]),[Delimiter=",", Columns=167, Encoding=65001, QuoteStyle=QuoteStyle.None]),
//Take first 2 rows and transform them
#"First two rows" = Table.FirstN(Source, 2),
#"Transpose rows" = Table.Transpose(#"First two rows"),
#"Add custom column" = Table.AddColumn(#"Transpose rows", "Custom", each if [Column1] = "" then [Column2] else [Column1]&"//"&[Column2]),
#"Remove 2 first columns" = Table.RemoveColumns(#"Add custom column",{"Column1", "Column2"}),
#"Transpose columns to rows" = Table.Transpose(#"Remove 2 first columns"),
#"Source without 2 first rows" = Table.RemoveFirstN(Source, 2),
#"Combine new rows with source" = Table.Combine({#"Transpose columns to rows",#"Source without 2 first rows"}),
#"Promoted first row as header" = Table.PromoteHeaders(#"Combine new rows with source", [PromoteAllScalars=true]),
// Filtering in XREVERSAL column (XREVERSAL = "" or XREVERSAL = null)
#"Filtered rows: XREVERSAL is empty" = Table.SelectRows(#"Promoted first row as header", each ([#"Rev. With//STBLG"] = "" or [#"Rev. With//STBLG"] = null)),
// Filtering DocumentNo column by excluding all documents that existing in exception listm, but only if E/L filter = TRUE
#"Filtered rows: applied exceptions list" = if #"E/L filter" = "" or #"E/L filter" = false then #"Filtered rows: XREVERSAL is empty" else Table.SelectRows(#"Filtered rows: XREVERSAL is empty", each not List.Contains(#"BELNR E/L", [#"DocumentNo//BELNR"])),
// Filtering by exceptions list
#"Filtered rows: document type" = if List.First(#"Document types list:")= "" or List.First(#"Document types list:")= -1 then #"Filtered rows: applied exceptions list" else Table.SelectRows(#"Filtered rows: applied exceptions list", each List.Contains(#"Document types list:", [#"Doc. Type//BLART"])),
#"Filtered rows: by Currency filter" = if #"Currency filter"= "" then #"Filtered rows: document type" else if Text.Contains(#"Currency filter","<>") then Table.SelectRows (#"Filtered rows: document type", each ([#"Crcy//WAERS"] <> Text.End(#"Currency filter",3))) else Table.SelectRows(#"Filtered rows: document type", each ([#"Crcy//WAERS"] = #"Currency filter")),
// Replace 751 to 0751 in CoCd column for doing correctly the future mapping
#"Replaced values: correcting matching below" = Table.ReplaceValue(#"Filtered rows: by Currency filter","751","0751",Replacer.ReplaceValue,{"CoCd//BUKRS"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced values: correcting matching below",{{"DocumentNo//BELNR", each Text.TrimStart(_,"0")}}),
#"Merged with BSEG" = Table.NestedJoin(#"Trimmed Text", {"DocumentNo//BELNR", "CoCd//BUKRS", "Year//GJAHR"}, #"0_BSEG", {"Document Number//BELNR", "Company Code//BUKRS", "Fiscal Year//GJAHR"}, "BSEG", JoinKind.LeftOuter),
#"Expanded BSEG" = Table.ExpandTableColumn(#"Merged with BSEG", "BSEG", {"Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK"}, {"Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK"}),
#"Filtered rows: HKONT" = Table.SelectRows(#"Expanded BSEG", each [#"G/L Account//HKONT"] <> "766000" and [#"G/L Account//HKONT"] <> "666000"),
#"Filtered rows: HKONT list" = if List.First(#"HKONT S/L")= null then #"Filtered rows: HKONT" else Table.SelectRows(#"Filtered rows: HKONT", each List.Contains(#"HKONT S/L", [#"G/L Account//HKONT"])),
#"Merged with Plan" = Table.NestedJoin(#"Filtered rows: HKONT list", {"G/L Account//HKONT"}, #"2_План счетов", {"счет HANA"}, "План счетов", JoinKind.LeftOuter),
#"Expanded Plan" = Table.ExpandTableColumn(#"Merged with Plan", "План счетов", {"Column1"}, {"Column1"}),
#"Merged with Codes" = Table.NestedJoin(#"Expanded Plan", {"Account Type//KOART", "Special G/L Ind.//UMSKZ"}, #"1_Codes", {"Вид счета", "КОД ОГК в Нессии"}, "Codes", JoinKind.LeftOuter),
#"Expanded Codes" = Table.ExpandTableColumn(#"Merged with Codes", "Codes", {"Код ОГК ms2"}, {"Код ОГК ms2"}),
#"Grouped Rows" = Table.Group(#"Expanded Codes", {"DocumentNo//BELNR"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Table", each _, type table [#"Cl.//MANDT"=text, #"CoCd//BUKRS"=text, #"DocumentNo//BELNR"=text, #"Year//GJAHR"=text, #"Doc. Type//BLART"=nullable text, #"Doc. Date//BLDAT"=text, #"Pstng Date//BUDAT"=text, #"Posting period//MONAT"=text, #"Entry Dte//CPUDT"=text, #"Time//CPUTM"=text, #"Changed//AEDAT"=text, #"Last Updte//UPDDT"=text, #"TranslDate//WWERT"=text, #"User Name//USNAM"=text, #"TCode//TCODE"=text, #"Cross-CC Number//BVORG"=text, #"Reference//XBLNR"=text, #"RecEnt Doc//DBBLG"=text, #"RecYear//DBBLG_GJAHR"=text, #"RecCoCode//DBBLG_BUKRS"=text, #"Rev. With//STBLG"=text, #"Year//STJAH"=text, #"Document Header Text//BKTXT"=text, #"Crcy//WAERS"=text, #"Exch.rate//KURSF"=text, #"GrpCy//KZWRS"=text, #"Group Exchange Rate//KZKRS"=text, #"S//BSTAT"=text, #"Net document type//XNETB"=text, #"Un.del.cts//FRATH"=text, #"I//XRUEB"=text, #"Tran//GLVOR"=text, #"Session Name//GRPID"=text, #"Document Name in the Archive System//DOKID"=text, #"ExtractID//ARCID"=text, #"DT//IBLAR"=text, #"Ref. proc.//AWTYP"=text, #"Object Key//AWKEY"=text, #"FMA//FIKRS"=text, #"LCurr//HWAER"=text, #"LCur2//HWAE2"=text, #"LCur3//HWAE3"=text, #"FX Rate 2//KURS2"=text, #"FX Rate 3//KURS3"=text, #"SC//BASW2"=text, #"SC//BASW3"=text, #"Translation Date//UMRD2"=text, #"Translation Date//UMRD3"=text, #"Reversal Flag//XSTOV"=text, #"Reversal Date//STODT"=text, #"Calculate Tax//XMWST"=text, #"CT//CURT2"=text, #"CT//CURT3"=text, #"ERTy//KUTY2"=text, #"ERTy//KUTY3"=text, #"Calculate Taxes on Net Amount//XSNET"=text, #"SCCd//AUSBK"=text, #"Tax details changed//XUSVR"=text, #"Status of Data Transfer to Next Release//DUEFL"=text, #"Log.System//AWSYS"=text, #"Rate for Taxes//TXKRS"=text, #"TaxRate:LC//CTXKRS"=text, #"Request No//LOTKZ"=text, #"Customer Bill/Ex. Paid Before Due Date//XWVOF"=text, #"Reason//STGRD"=text, #"Parked By//PPNAM"=text, #"Parking Date//PPDAT"=text, #"Time//PPTME"=text, #"Parked with//PPTCOD"=text, #"Branch number//BRNCH"=text, #"Pages//NUMPG"=text, #"dis. doc.//ADISC"=text, #"Ref.key (header) 1//XREF1_HD"=text, #"Ref.key (header) 2//XREF2_HD"=text, #"Reversal//XREVERSAL"=text, #"IR date//REINDAT"=text, #"Ld//RLDNR"=text, #"Ledger Group//LDGRP"=text, #"Mand.//PROPMANO"=text, #"Alt Refer Number//XBLNR_ALT"=text, #"Tax Reporting Date//VATDATE"=text, #"Doc.Type//DOCCAT"=text, #"Split Posting//XSPLIT"=text, #"Cash Flow-Relevant Doc.//CASH_ALLOC"=text, #"Follow-on document indicator//FOLLOW_ON"=text, #"Reorg//XREORG"=text, #"Subs//SUBSET"=text, #"ERTy//KURST"=text, #"Market Data Exchange Rate//KURSX"=text, #"Market Data Exchange Rate 2//KUR2X"=text, #"Market Data Exchange Rate 3//KUR3X"=text, #"Document Originates from Multi Currency//XMCA"=text, #"Resubmission Date//RESUBMISSION"=text, #"SendLogSys//LOGSYSTEM_SENDER"=text, #"SenderCCde//BUKRS_SENDER"=text, #"SendDocNo.//BELNR_SENDER"=text, #"Sender FY//GJAHR_SENDER"=text, #"SubID//INTSUBID"=text, #"Rev. Org.//AWORG_REV"=text, #"Rev. Ref.//AWREF_REV"=text, #"RvD//XREVERSING"=text, #"Rev//XREVERSED"=text, #"GL BtGrp//GLBTGRP"=text, #"BTran//CO_VRGNG"=text, #"RT//CO_REFBT"=text, #"ALE: DocNr//CO_ALEBN"=text, #"Val. Date//CO_VALDT"=text, #"SCODocNo//CO_BELNR_SENDER"=text, #"SendCOAr//KOKRS_SENDER"=text, #"AccP//ACC_PRINCIPLE"=text, #"Data Filter Value for Data Aging//_DATAAGING"=text, #"Transfer Variant//TRAVA_PN"=text, #"LDGRP-Specific//LDGRPSPEC_PN"=text, #"Area Limitation//AFABESPEC_PN"=text, #"Sec//XSECONDARY"=text, #"Rep St//REPROCESSING_STATUS_CODE"=text, #"Partial//TRR_PARTIAL_IND"=text, #"RemSt//ITEM_REMOVAL_STATUS"=text, #"LR//PENRC"=text, #"Country Specific Reference 1//GLO_REF1_HD"=text, #"Country Specific Date 1//GLO_DAT1_HD"=text, #"Country Specific Reference 2//GLO_REF2_HD"=text, #"Country Specific Date 2//GLO_DAT2_HD"=text, #"Country Specific Reference 3//GLO_REF3_HD"=text, #"Country Specific Date 3//GLO_DAT3_HD"=text, #"Country Specific Reference 4//GLO_REF4_HD"=text, #"Country Specific Date 4//GLO_DAT4_HD"=text, #"Country Specific Reference 5//GLO_REF5_HD"=text, #"Country Specific Date 5//GLO_DAT5_HD"=text, #"Country Specific Business Partner 1//GLO_BP1_HD"=text, #"Country Specific Business Partner 2//GLO_BP2_HD"=text, #"invoice type (for annexation)//ANXTYPE"=text, #"annexation amount//ANXAMNT"=text, #"annexation percentage//ANXPERC"=text, #"V.A.T indicator//ZVAT_INDC"=text, #"Document Status///SAPF15/STATUS"=text, #"RT//PSOTY"=text, #"Reason//PSOAK"=text, #"Region//PSOKS"=text, #"S//PSOSG"=text, #"File number//PSOFN"=text, #"IF//INTFORM"=text, #"Interest Calc. Date//INTDATE"=text, #"Posting Day//PSOBT"=text, #"Act//PSOZL"=text, #"Chngd On//PSODT"=text, #"Time//PSOTM"=text, #"Transfer type//FM_UMART"=text, #"CTyp//CCINS"=text, #"Card number//CCNUM"=text, #"Payment Statistical Sampling Block//SSBLK"=text, #"Lot//BATCH"=text, #"User Name//SNAME"=text, #"Sampled invoice by Payment Stat. Samplin//SAMPLED"=text, #"PPA Ex.//EXCLUDE_FLAG"=text, #"BL Ind.//BLIND"=text, #"Offset Status//OFFSET_STATUS"=text, #"Date Referred to Treasury//OFFSET_REFER_DAT"=text, #"Doc.Cond.//KNUMV"=text, #"DocumentBlock//BLO"=text, #"Contract No//CNT"=text, #"Doc Type//PYBASTYP"=text, #"Doc No.//PYBASNO"=text, #"Date//PYBASDAT"=text, #"IBAN//PYIBAN"=text, #"Incg Nmbr//INWARDNO_HD"=text, #"Incg Date//INWARDDT_HD"=text, #"Account Type//KOART"=text, #"Amount//WRBTR"=nullable text, #"Amt.in loc.cur.//DMBTR"=nullable text, #"Assignment//ZUONR"=text, #"Baseline Payment Dte//ZFBDT"=text, #"Clearing Document//AUGBL"=text, #"Cost Center//KOSTL"=text, #"Customer//KUNNR"=text, #"G/L Account//HKONT"=text, #"Posting Key//BSCHL"=text, #"Profit Center//PRCTR"=text, #"Reference Key 1//XREF1"=text, #"Reference Key 2//XREF2"=text, #"Special G/L Ind.//UMSKZ"=text, #"Tax Code//MWSKZ"=text, #"Text//SGTXT"=text, #"Trading partner//VBUND"=text, #"Vendor//LIFNR"=text, #"WBS Element//PROJK"=text, Column1=nullable number, Код ОГК ms2=nullable text]}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Cl.//MANDT", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Posting period//MONAT", "Entry Dte//CPUDT", "Time//CPUTM", "Changed//AEDAT", "Last Updte//UPDDT", "TranslDate//WWERT", "User Name//USNAM", "TCode//TCODE", "Cross-CC Number//BVORG", "Reference//XBLNR", "RecEnt Doc//DBBLG", "RecYear//DBBLG_GJAHR", "RecCoCode//DBBLG_BUKRS", "Rev. With//STBLG", "Year//STJAH", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "GrpCy//KZWRS", "Group Exchange Rate//KZKRS", "S//BSTAT", "Net document type//XNETB", "Un.del.cts//FRATH", "I//XRUEB", "Tran//GLVOR", "Session Name//GRPID", "Document Name in the Archive System//DOKID", "ExtractID//ARCID", "DT//IBLAR", "Ref. proc.//AWTYP", "Object Key//AWKEY", "FMA//FIKRS", "LCurr//HWAER", "LCur2//HWAE2", "LCur3//HWAE3", "FX Rate 2//KURS2", "FX Rate 3//KURS3", "SC//BASW2", "SC//BASW3", "Translation Date//UMRD2", "Translation Date//UMRD3", "Reversal Flag//XSTOV", "Reversal Date//STODT", "Calculate Tax//XMWST", "CT//CURT2", "CT//CURT3", "ERTy//KUTY2", "ERTy//KUTY3", "Calculate Taxes on Net Amount//XSNET", "SCCd//AUSBK", "Tax details changed//XUSVR", "Status of Data Transfer to Next Release//DUEFL", "Log.System//AWSYS", "Rate for Taxes//TXKRS", "TaxRate:LC//CTXKRS", "Request No//LOTKZ", "Customer Bill/Ex. Paid Before Due Date//XWVOF", "Reason//STGRD", "Parked By//PPNAM", "Parking Date//PPDAT", "Time//PPTME", "Parked with//PPTCOD", "Branch number//BRNCH", "Pages//NUMPG", "dis. doc.//ADISC", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Reversal//XREVERSAL", "IR date//REINDAT", "Ld//RLDNR", "Ledger Group//LDGRP", "Mand.//PROPMANO", "Alt Refer Number//XBLNR_ALT", "Tax Reporting Date//VATDATE", "Doc.Type//DOCCAT", "Split Posting//XSPLIT", "Cash Flow-Relevant Doc.//CASH_ALLOC", "Follow-on document indicator//FOLLOW_ON", "Reorg//XREORG", "Subs//SUBSET", "ERTy//KURST", "Market Data Exchange Rate//KURSX", "Market Data Exchange Rate 2//KUR2X", "Market Data Exchange Rate 3//KUR3X", "Document Originates from Multi Currency//XMCA", "Resubmission Date//RESUBMISSION", "SendLogSys//LOGSYSTEM_SENDER", "SenderCCde//BUKRS_SENDER", "SendDocNo.//BELNR_SENDER", "Sender FY//GJAHR_SENDER", "SubID//INTSUBID", "Rev. Org.//AWORG_REV", "Rev. Ref.//AWREF_REV", "RvD//XREVERSING", "Rev//XREVERSED", "GL BtGrp//GLBTGRP", "BTran//CO_VRGNG", "RT//CO_REFBT", "ALE: DocNr//CO_ALEBN", "Val. Date//CO_VALDT", "SCODocNo//CO_BELNR_SENDER", "SendCOAr//KOKRS_SENDER", "AccP//ACC_PRINCIPLE", "Data Filter Value for Data Aging//_DATAAGING", "Transfer Variant//TRAVA_PN", "LDGRP-Specific//LDGRPSPEC_PN", "Area Limitation//AFABESPEC_PN", "Sec//XSECONDARY", "Rep St//REPROCESSING_STATUS_CODE", "Partial//TRR_PARTIAL_IND", "RemSt//ITEM_REMOVAL_STATUS", "LR//PENRC", "Country Specific Reference 1//GLO_REF1_HD", "Country Specific Date 1//GLO_DAT1_HD", "Country Specific Reference 2//GLO_REF2_HD", "Country Specific Date 2//GLO_DAT2_HD", "Country Specific Reference 3//GLO_REF3_HD", "Country Specific Date 3//GLO_DAT3_HD", "Country Specific Reference 4//GLO_REF4_HD", "Country Specific Date 4//GLO_DAT4_HD", "Country Specific Reference 5//GLO_REF5_HD", "Country Specific Date 5//GLO_DAT5_HD", "Country Specific Business Partner 1//GLO_BP1_HD", "Country Specific Business Partner 2//GLO_BP2_HD", "invoice type (for annexation)//ANXTYPE", "annexation amount//ANXAMNT", "annexation percentage//ANXPERC", "V.A.T indicator//ZVAT_INDC", "Document Status///SAPF15/STATUS", "RT//PSOTY", "Reason//PSOAK", "Region//PSOKS", "S//PSOSG", "File number//PSOFN", "IF//INTFORM", "Interest Calc. Date//INTDATE", "Posting Day//PSOBT", "Act//PSOZL", "Chngd On//PSODT", "Time//PSOTM", "Transfer type//FM_UMART", "CTyp//CCINS", "Card number//CCNUM", "Payment Statistical Sampling Block//SSBLK", "Lot//BATCH", "User Name//SNAME", "Sampled invoice by Payment Stat. Samplin//SAMPLED", "PPA Ex.//EXCLUDE_FLAG", "BL Ind.//BLIND", "Offset Status//OFFSET_STATUS", "Date Referred to Treasury//OFFSET_REFER_DAT", "Doc.Cond.//KNUMV", "DocumentBlock//BLO", "Contract No//CNT", "Doc Type//PYBASTYP", "Doc No.//PYBASNO", "Date//PYBASDAT", "IBAN//PYIBAN", "Incg Nmbr//INWARDNO_HD", "Incg Date//INWARDDT_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK", "Column1", "Код ОГК ms2"}, {"Cl.//MANDT", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Posting period//MONAT", "Entry Dte//CPUDT", "Time//CPUTM", "Changed//AEDAT", "Last Updte//UPDDT", "TranslDate//WWERT", "User Name//USNAM", "TCode//TCODE", "Cross-CC Number//BVORG", "Reference//XBLNR", "RecEnt Doc//DBBLG", "RecYear//DBBLG_GJAHR", "RecCoCode//DBBLG_BUKRS", "Rev. With//STBLG", "Year//STJAH", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "GrpCy//KZWRS", "Group Exchange Rate//KZKRS", "S//BSTAT", "Net document type//XNETB", "Un.del.cts//FRATH", "I//XRUEB", "Tran//GLVOR", "Session Name//GRPID", "Document Name in the Archive System//DOKID", "ExtractID//ARCID", "DT//IBLAR", "Ref. proc.//AWTYP", "Object Key//AWKEY", "FMA//FIKRS", "LCurr//HWAER", "LCur2//HWAE2", "LCur3//HWAE3", "FX Rate 2//KURS2", "FX Rate 3//KURS3", "SC//BASW2", "SC//BASW3", "Translation Date//UMRD2", "Translation Date//UMRD3", "Reversal Flag//XSTOV", "Reversal Date//STODT", "Calculate Tax//XMWST", "CT//CURT2", "CT//CURT3", "ERTy//KUTY2", "ERTy//KUTY3", "Calculate Taxes on Net Amount//XSNET", "SCCd//AUSBK", "Tax details changed//XUSVR", "Status of Data Transfer to Next Release//DUEFL", "Log.System//AWSYS", "Rate for Taxes//TXKRS", "TaxRate:LC//CTXKRS", "Request No//LOTKZ", "Customer Bill/Ex. Paid Before Due Date//XWVOF", "Reason//STGRD", "Parked By//PPNAM", "Parking Date//PPDAT", "Time//PPTME", "Parked with//PPTCOD", "Branch number//BRNCH", "Pages//NUMPG", "dis. doc.//ADISC", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Reversal//XREVERSAL", "IR date//REINDAT", "Ld//RLDNR", "Ledger Group//LDGRP", "Mand.//PROPMANO", "Alt Refer Number//XBLNR_ALT", "Tax Reporting Date//VATDATE", "Doc.Type//DOCCAT", "Split Posting//XSPLIT", "Cash Flow-Relevant Doc.//CASH_ALLOC", "Follow-on document indicator//FOLLOW_ON", "Reorg//XREORG", "Subs//SUBSET", "ERTy//KURST", "Market Data Exchange Rate//KURSX", "Market Data Exchange Rate 2//KUR2X", "Market Data Exchange Rate 3//KUR3X", "Document Originates from Multi Currency//XMCA", "Resubmission Date//RESUBMISSION", "SendLogSys//LOGSYSTEM_SENDER", "SenderCCde//BUKRS_SENDER", "SendDocNo.//BELNR_SENDER", "Sender FY//GJAHR_SENDER", "SubID//INTSUBID", "Rev. Org.//AWORG_REV", "Rev. Ref.//AWREF_REV", "RvD//XREVERSING", "Rev//XREVERSED", "GL BtGrp//GLBTGRP", "BTran//CO_VRGNG", "RT//CO_REFBT", "ALE: DocNr//CO_ALEBN", "Val. Date//CO_VALDT", "SCODocNo//CO_BELNR_SENDER", "SendCOAr//KOKRS_SENDER", "AccP//ACC_PRINCIPLE", "Data Filter Value for Data Aging//_DATAAGING", "Transfer Variant//TRAVA_PN", "LDGRP-Specific//LDGRPSPEC_PN", "Area Limitation//AFABESPEC_PN", "Sec//XSECONDARY", "Rep St//REPROCESSING_STATUS_CODE", "Partial//TRR_PARTIAL_IND", "RemSt//ITEM_REMOVAL_STATUS", "LR//PENRC", "Country Specific Reference 1//GLO_REF1_HD", "Country Specific Date 1//GLO_DAT1_HD", "Country Specific Reference 2//GLO_REF2_HD", "Country Specific Date 2//GLO_DAT2_HD", "Country Specific Reference 3//GLO_REF3_HD", "Country Specific Date 3//GLO_DAT3_HD", "Country Specific Reference 4//GLO_REF4_HD", "Country Specific Date 4//GLO_DAT4_HD", "Country Specific Reference 5//GLO_REF5_HD", "Country Specific Date 5//GLO_DAT5_HD", "Country Specific Business Partner 1//GLO_BP1_HD", "Country Specific Business Partner 2//GLO_BP2_HD", "invoice type (for annexation)//ANXTYPE", "annexation amount//ANXAMNT", "annexation percentage//ANXPERC", "V.A.T indicator//ZVAT_INDC", "Document Status///SAPF15/STATUS", "RT//PSOTY", "Reason//PSOAK", "Region//PSOKS", "S//PSOSG", "File number//PSOFN", "IF//INTFORM", "Interest Calc. Date//INTDATE", "Posting Day//PSOBT", "Act//PSOZL", "Chngd On//PSODT", "Time//PSOTM", "Transfer type//FM_UMART", "CTyp//CCINS", "Card number//CCNUM", "Payment Statistical Sampling Block//SSBLK", "Lot//BATCH", "User Name//SNAME", "Sampled invoice by Payment Stat. Samplin//SAMPLED", "PPA Ex.//EXCLUDE_FLAG", "BL Ind.//BLIND", "Offset Status//OFFSET_STATUS", "Date Referred to Treasury//OFFSET_REFER_DAT", "Doc.Cond.//KNUMV", "DocumentBlock//BLO", "Contract No//CNT", "Doc Type//PYBASTYP", "Doc No.//PYBASNO", "Date//PYBASDAT", "IBAN//PYIBAN", "Incg Nmbr//INWARDNO_HD", "Incg Date//INWARDDT_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK", "Column1", "Код ОГК ms2"}),
//Filtering displayed rows: if ALL or null then full stack of rows; if 2 then only pair rows; if >2 then all rows except pair rows
#"Filtered rows: ALL, >2 or 2" = if #"R/C filter" = "" then #"Expanded Table" else if #"R/C filter" = 2 then Table.SelectRows(#"Expanded Table", each [Count] = 2) else if #"R/C filter" = 3 then Table.SelectRows(#"Expanded Table", each [Count] = 3) else Table.SelectRows(#"Expanded Table", each [Count] > 2),
#"Replaced values: point to nothing" = Table.ReplaceValue(#"Filtered rows: ALL, >2 or 2",".",",",Replacer.ReplaceText,{"Exch.rate//KURSF"}),
// Replace ZR to BR (KZ=KZ, DZ=DZ; stay as it was)
#"Replaced ZR to BR" = Table.ReplaceValue(#"Replaced values: point to nothing","ZR","BR",Replacer.ReplaceText,{"Doc. Type//BLART"}),
#"Created NEWKO" = Table.AddColumn(#"Replaced ZR to BR", "Custom", each if [#"Account Type//KOART"] = "D" then [#"Customer//KUNNR"] else if [#"Account Type//KOART"] = "K" then [#"Vendor//LIFNR"] else [Column1]),
#"Created Custom Profit Center" = Table.AddColumn(#"Created NEWKO", "Custom Profit Center", each if [#"WBS Element//PROJK"] = "" and [#"Cost Center//KOSTL"] = "" then "DEF-TECH" else [#"Profit Center//PRCTR"]),
#"Changed type: NEWKO" = Table.TransformColumnTypes(#"Created Custom Profit Center",{{"Custom", type text}}),
#"Change type with Locale (Currency)" = Table.TransformColumnTypes(#"Changed type: NEWKO", {{"Exch.rate//KURSF", type number}}, "ru-RU"),
#"Removed Other Columns" = Table.SelectColumns(#"Change type with Locale (Currency)",{"DocumentNo//BELNR", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Reference//XBLNR", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "Calculate Tax//XMWST", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Cost Center//KOSTL", "Posting Key//BSCHL", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "WBS Element//PROJK", "Код ОГК ms2", "G/L Account//HKONT", "Custom", "Custom Profit Center", "Clearing Document//AUGBL"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"DocumentNo//BELNR", "Year//GJAHR", "Doc. Date//BLDAT", "Doc. Type//BLART", "CoCd//BUKRS", "Pstng Date//BUDAT", "Crcy//WAERS", "Exch.rate//KURSF", "Reference//XBLNR", "Document Header Text//BKTXT", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Calculate Tax//XMWST", "Posting Key//BSCHL", "G/L Account//HKONT", "Custom", "Trading partner//VBUND", "Код ОГК ms2", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Tax Code//MWSKZ", "Assignment//ZUONR", "Text//SGTXT", "Cost Center//KOSTL", "Custom Profit Center", "WBS Element//PROJK", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Baseline Payment Dte//ZFBDT"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"G/L Account//HKONT", "G/L OLD"},{"Custom", "G/L Account//HKONT"}, {"Код ОГК ms2", "SG"}, {"Custom Profit Center", "Profit Center//PRCTR"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"DocumentNo//BELNR", Order.Ascending}, {"G/L OLD", Order.Descending}})
in
#"Sorted Rows"
Zagadka, спасибо, принял к сведению эту информацию, но все равно пока не пойму, что делать: сначала объединить все нужное в один запрос, забуферить, после чего фильтровать это все по разным условиям, либо сначала частично отфильтровать, забуферить отфильтрованное до, после чего продолжить видоизменять промежуточный результат.
Framed, по сути буфер нужен только в случае необходимости избежания повторного чтения источника. Если такого в процессе выполнения кода нет, то он на фиг не нужен, наоборот, только ухудшает.
PooHkrd, да, спасибо, практическим путем удалось выяснить, что конкретно в моем случае, буферизация не подходит. А вообще очень помогла информация из тем, в обсуждении которых вы непосредственно участвовали.