Cov ntawv hloov pauv ntau hauv Power Query nrog List.Accumulate function

Yuav ua li cas sai thiab nyob rau hauv ntau hloov cov ntawv nyeem raws li cov ntaub ntawv siv nrog cov qauv - peb twb txheeb nws tawm. Tam sim no cia sim ua nws hauv Power Query.

Raws li feem ntau tshwm sim Ua txoj haujlwm no yooj yim dua li piav qhia yog vim li cas nws ua haujlwm, tab sis cia peb sim ua ob qho 🙂

Yog li, peb muaj ob lub "ntse" dynamic cov lus tsim los ntawm cov kab ke zoo tib yam nrog cov keyboard shortcut Ctrl+T los yog pab neeg Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table):

Kuv hu thawj lub rooj Cov ntaub ntawv, ob lub rooj - Directorysiv teb Lub npe (Table npe) tab constructor (Tsim).

Task: hloov chaw nyob hauv lub rooj Cov ntaub ntawv txhua qhov tshwm sim los ntawm kab Yuav kom pom Phau Ntawv rau lawv cov sib raug zoo counterparts los ntawm kem Hloov chaw. Tus so ntawm cov ntawv nyeem nyob rau hauv lub hlwb yuav tsum nyob twj ywm untouched.

Kauj ruam 1. Thawb cov npe rau hauv Hwj chim Query thiab tig mus rau hauv ib daim ntawv teev

Tom qab teeb tsa lub xov tooj ntawm tes mus rau txhua qhov chaw hauv cov lus siv, nyem rau ntawm lub tab Cov ntaub ntawv (Hnub)los yog ntawm lub tab Lub Zog Lus Nug (yog tias koj muaj ib qho qub version ntawm Excel thiab koj tau nruab Power Query raws li ib qho ntxiv-nyob rau hauv ib qho tab) ntawm lub pob Los ntawm lub rooj / ntau yam (Los ntawm Table/Range).

Cov lus siv yuav raug muab tso rau hauv Power Query query editor:

Yuav kom tsis txhob cuam tshuam, ib kauj ruam ntxiv hloov hom (Hloov hom) nyob rau hauv txoj cai vaj huam sib luag, cov kauj ruam uas siv tau yuav xyuam xim deleted, tawm hauv cov kauj ruam xwb Tau qhov twg los (Tau qhov twg los):

Tam sim no, txhawm rau ua qhov hloov pauv thiab hloov pauv ntxiv, peb yuav tsum tig lub rooj no mus rau hauv cov npe (cov npe).

Lyrical digression

• rooj yog ob sab array uas muaj ob peb kab thiab kab.
• Cov Ntaub Ntawv (Record) - ib-dimensional array-txoj hlua, muaj ntau lub teb-cov ntsiab lus nrog cov npe, piv txwv li [Npe = "Masha", Poj niam txiv neej = "f", Hnub nyoog = 25]
  
• daim ntawv teev - ib-dimensional array-kem, muaj ntau yam, piv txwv li {1, 2, 3, 10, 42} or {“Kev Ntseeg Cia Siab Kev Hlub”}
  

Yuav kom daws tau peb qhov teeb meem, peb yuav nyiam nyiam hom daim ntawv teev.

Qhov ua kom yuam kev ntawm no yog cov npe khoom hauv Power Query tuaj yeem tsis tsuas yog tus lej banal lossis ntawv nyeem, tab sis kuj muaj lwm cov npe lossis cov ntaub ntawv. Nws yog nyob rau hauv xws li ib daim ntawv qhia tsis yooj yim (cov npe), suav nrog cov ntaub ntawv (cov ntaub ntawv) uas peb yuav tsum tau tig peb cov npe. Nyob rau hauv Power Query syntactic notation ( nkag hauv square brackets, cov npe hauv curly brackets) qhov no yuav zoo li:

{

    [ Nrhiav = “St. Petersburg", Hloov = "St. Petersburg"] ,

    [ Nrhiav = “St. Petersburg", Hloov = "St. Petersburg"] ,

    [ Nrhiav = “Peter”, Hloov = “St. Petersburg"] ,

thiab lwm yam

}

Qhov kev hloov pauv zoo li no tau ua los ntawm kev siv lub luag haujlwm tshwj xeeb ntawm M hom lus ua rau Power Query - Table.ToRecords. Txhawm rau siv nws ncaj qha rau hauv daim ntawv qhia bar, ntxiv cov haujlwm no rau cov kauj ruam code muaj Tau qhov twg los.

Nws yog:

Tom qab:

Tom qab ntxiv Table.ToRecords muaj nuj nqi, cov tsos ntawm peb lub rooj yuav hloov - nws yuav tig mus rau hauv ib daim ntawv teev cov ntaub ntawv. Cov ntsiab lus ntawm tus kheej cov ntaub ntawv tuaj yeem pom nyob hauv qab ntawm qhov pom pane los ntawm txhaj rau hauv lub cell tom qab ntawm ib lo lus Cov ntaub ntawv (tab sis tsis nyob hauv ib lo lus!)

Ntxiv rau qhov saum toj no, nws ua rau kev txiav txim siab ntxiv ib qho mob stroke ntxiv - rau cache (tsis) peb cov npe tsim. Qhov no yuav yuam kom Power Query thauj peb cov npe nrhiav ib zaug rau hauv lub cim xeeb thiab tsis suav rov qab rov qab thaum peb nkag mus rau tom qab hloov nws. Txhawm rau ua qhov no, qhwv peb cov qauv hauv lwm txoj haujlwm - List.Buffer:

Xws li caching yuav ua rau pom kev nce nrawm heev (los ntawm ob peb zaug!) nrog rau ntau cov ntaub ntawv thawj zaug kom tshem tawm.

Qhov no ua tiav qhov kev npaj ntawm phau ntawv qhia.

Nws tseem nyem rau Tsev - Kaw thiab Load - Kaw thiab Load rau… (Lub Tsev - Kaw & Load - Kaw & Load rau ..), xaiv ib qho kev xaiv Tsuas yog tsim kev sib txuas (tsuas yog tsim kev sib txuas) thiab rov qab mus rau Excel.

Kauj ruam 2. Thauj cov ntaub ntawv rooj

Txhua yam yog trite ntawm no. Raws li ua ntej nrog cov ntaub ntawv siv, peb nce mus rau txhua qhov chaw hauv lub rooj, nyem rau ntawm lub tab Cov ntaub ntawv khawm Los ntawm Table/Range thiab peb lub rooj Cov ntaub ntawv nkag rau hauv Power Query. Tsis siv neeg ntxiv cov kauj ruam hloov hom (Hloov hom) koj tseem tuaj yeem tshem tawm:

Tsis muaj kev npaj tshwj xeeb yuav tsum tau ua nrog nws, thiab peb mus rau qhov tseem ceeb tshaj plaws.

Kauj Ruam 3. Ua kev hloov pauv siv cov List.Accumulate muaj nuj nqi

Cia peb ntxiv ib kem xam rau peb cov ntaub ntawv cov lus siv cov lus txib Ntxiv ib Kem – Custom Column (Ntxiv kab - Custom column): thiab sau lub npe ntawm kab ntawv ntxiv hauv lub qhov rai uas qhib (piv txwv li, kho qhov chaw nyob) thiab peb txoj haujlwm ua khawv koob List.Accumulate:

Nws tseem nyem rau OK - thiab peb tau txais ib kab nrog cov kev hloov pauv:

Nco ntsoov tias:

• Txij li thaum Power Query yog qhov teeb meem, tsis muaj kev hloov pauv hauv kab kawg, vim tias hauv phau ntawv peb muaj "SPb", tsis yog "SPb".
• Yog tias muaj ntau txoj hlua los hloov ib zaug hauv cov ntaub ntawv (piv txwv li, hauv kab 7 koj yuav tsum hloov ob qho "S-Pb" thiab "Prospectus"), ces qhov no tsis tsim teeb meem (tsis zoo li hloov nrog cov qauv los ntawm txoj kev dhau los).
• Yog tias tsis muaj dab tsi los hloov hauv cov ntawv nyeem (kab 9), ces tsis muaj qhov yuam kev tshwm sim (tsis zoo li, dua, los ntawm kev hloov los ntawm cov qauv).

Qhov ceev ntawm qhov kev thov no yog heev, zoo heev. Piv txwv li, rau lub rooj ntawm cov ntaub ntawv thawj zaug nrog qhov loj ntawm 5000 kab, cov lus nug no tau hloov kho nyob rau hauv tsawg tshaj li ib ob (tsis muaj buffering, los ntawm txoj kev, txog 3 vib nas this!)

Yuav ua li cas cov List.Accumulate muaj nuj nqi ua hauj lwm

Hauv txoj ntsiab cai, qhov no tuaj yeem yog qhov kawg (rau kuv sau, thiab rau koj nyeem) kab lus no. Yog hais tias koj xav tsis tau tsuas yog muaj peev xwm, tab sis kuj to taub yuav ua li cas nws ua hauj lwm "nyob rau hauv lub hood", ces koj yuav tau mus dhia ib tug me ntsis ntxaum mus rau hauv lub luav qhov thiab deal nrog rau cov List.Accumulate muaj nuj nqi, uas ua tag nrho cov bulk hloov. ua haujlwm rau peb.

Lub syntax rau txoj haujlwm no yog:

=List.Accumulate(daim ntawv teev, noob, accumulator)

qhov twg

• daim ntawv teev yog cov npe uas nws cov ntsiab lus peb tab tom iterating dua. 
• noob - thawj lub xeev
• accumulator - ib qho kev ua haujlwm uas ua rau qee qhov haujlwm ( lej, ntawv nyeem, thiab lwm yam) ntawm cov khoom tom ntej ntawm daim ntawv thiab sau cov txiaj ntsig ntawm kev ua haujlwm hauv qhov sib txawv tshwj xeeb.
  

Feem ntau, cov syntax rau kev sau cov haujlwm hauv Power Query zoo li no:

(argument1, argument2, … argumentN) => qee qhov kev ua nrog kev sib cav

Piv txwv li, lub luag haujlwm ntawm kev suav tuaj yeem sawv cev raws li:

(a, b) => ib + b

Rau List.Accumulate , qhov no accumulator muaj nuj nqi muaj ob qhov yuav tsum tau sib cav (lawv tuaj yeem hu ua dab tsi, tab sis cov npe ib txwm muaj. lub xeev и tam sim no, raws li nyob rau hauv lub official pab rau lub luag haujlwm no, qhov twg:

• lub xeev - qhov sib txawv uas qhov tshwm sim tau sau tseg (nws tus nqi pib yog qhov tau hais los saum toj no noob)
  
• tam sim no - tus nqi iterated tom ntej ntawm daim ntawv daim ntawv teev
  

Piv txwv li, cia peb saib cov kauj ruam ntawm lub logic ntawm kev tsim kho hauv qab no:

=List.Accumulate({3, 2, 5}, 10, (lub xeev, tam sim no) => xeev + tam sim no)

Qhov no yog qhov tseeb sau lub xeev tus nqi yog peb List.Accumulate muaj nuj nqi thiab outputs raws li ib tug tshwm sim:

Yog tias koj fantasize me ntsis, ces siv List.Accumulate muaj nuj nqi, koj tuaj yeem simulate, piv txwv li, Excel muaj nuj nqi CONCATENATE (hauv Power Query, nws cov analogue hu ua Text.Ua ke) siv cov lus qhia:

Los yog txawm nrhiav tus nqi siab tshaj plaws (kev coj ua ntawm Excel's MAX muaj nuj nqi, uas nyob rau hauv Power Query hu ua List.Max):

Txawm li cas los xij, qhov tseem ceeb ntawm List.Accumulate yog lub peev xwm los ua cov ntawv tsis yog cov ntawv yooj yim lossis cov npe lej raws li kev sib cav, tab sis cov khoom siv ntau dua - piv txwv li, cov npe-los ntawm cov npe lossis cov npe-ntawm-cov ntaub ntawv (nyob zoo, Cov Ntawv Qhia!)

Cia peb saib dua ntawm kev tsim kho uas tau ua qhov hloov pauv hauv peb qhov teeb meem:

List.Accumulate(Directory, [Chaw Nyob], (state, tam sim no) => Text.Replace(state, current[Nrhiav], current[Replace]) )

Dab tsi tshwm sim ntawm no?

1. Raws li tus nqi pib (noob) peb coj thawj cov ntawv tsis txaus ntseeg los ntawm kab ntawv [Chaw Nyob] peb lub rooj: 199034, St. Petersburg, str. Beringa, d. 1
2. Ces List.Accumulate iterates hla lub ntsiab ntawm daim ntawv ib los ntawm ib tug – Phau Ntawv. Txhua lub ntsiab lus ntawm daim ntawv teev npe no yog cov ntaub ntawv uas muaj ob lub teb "Yuav ua li cas nrhiav tau - Dab tsi los hloov nrog" lossis, hauv lwm lo lus, kab tom ntej hauv phau ntawv qhia.
3. Lub accumulator muaj nuj nqi tso rau hauv qhov sib txawv lub xeev pib tus nqi (thawj chaw nyob 199034, St. Petersburg, str. Beringa, d. 1) thiab ua ib qho accumulator muaj nuj nqi rau nws - kev hloov pauv siv tus qauv M-function Text. Hloov (analogous rau Excel's SUBSTITUTE muaj nuj nqi). Nws syntax yog:

   Text.Replace( thawj cov ntawv nyeem, peb tab tom nrhiav, dab tsi peb hloov nrog )

   thiab ntawm no peb muaj:

• lub xeev yog peb qhov chaw nyob qias neeg, uas nyob hauv lub xeev (tau los ntawm noob)
  
• tam sim no [Tshawb nrhiav] - teb tus nqi Yuav kom pom los ntawm qhov kev nkag iterated tom ntej ntawm daim ntawv teev npe Directory, uas nyob rau hauv qhov sib txawv tam sim no
• tam sim no [Hloov] - teb tus nqi Hloov chaw los ntawm qhov kev nkag iterated tom ntej ntawm daim ntawv teev npe Directorypw hauv tam sim no

Yog li, rau txhua qhov chaw nyob, tag nrho lub voj voog ntawm kev suav ntawm txhua kab hauv cov npe tau khiav txhua lub sijhawm, hloov cov ntawv los ntawm [Nrhiav] teb nrog tus nqi los ntawm [Hloov] teb.

Vam tias koj tau txais lub tswv yim 🙂

• Tej zaum hloov cov ntawv hauv ib daim ntawv uas siv cov qauv
  
• Cov Lus Qhia tsis tu ncua (RegExp) hauv Cov Lus Nug Lub Zog