Opgelost Waardes vervangen op basis van meerdere dynamische condities en kolommen in Power Query

[Hela1966]

Goedemorgen,
Momenteel gebruik ik "hardcoded" waardes in Power Query (Windows 11, Office 365) om waardes te vervangen indien aan condities wordt voldaan.
Dat werkte tot nu toe prima, maar het zou uiteraard fijn zijn als de vervangingen dynamisch (vanuit een andere tabel) gedaan zouden worden (zeker gezien het feit dat het aantal alleen maar toeneemt)
In aangehecht bestand staat ook alle data, het gewenste resultaat en de huidige M-code.

Brondata:

Naam	Bedrag​	TAGS​	Mededelingen
Hans	25,00 ​	2​	zomer
Grietje	25,00 ​	2​	blauw
Hans2	25,00 ​	4​	groen
Grietje2	25,00 ​	8​	lente
BlueEyes	-140,00 ​	7​	blah die blah
SAM	3,00 ​	1​	blah die blah
Extraatje	10,00 ​	2​	bladiebla
Extraatje 2	100,00 ​	2​	bla die bla GO bla
Sem	25,00 ​	2​	Studies
SAM	50,00 ​	2​	Mijn Studie

Dynamische condities:

Nr​	Naam	Bedrag​	Mededelingen​	NwTag​
1​	Hans	25,00 ​		0​
2​	Grietje	25,00 ​		0​
3​	SAM	3,00 ​		7​
4​	BleuEyes	-140,00 ​		4​
5​	Extraatje			9​
6​			GO​	3​
7​	SAM		Studie​	1​

Dit zijn de condities waaraan voldaan moet worden:
1. Als Naam EN Bedrag voorkomen in de dynamische tabel
dan de waarde uit kolom TAGS vervangen met de waarde uit NwTag
(dit zijn dus nr 1 t/m 4 uit het voorbeeld)

2. Als Naam voorkomt EN Mededelingen de exacte tekst BEVAT uit de dynamische tabel
dan de waarde uit kolom TAGS vervangen met de waarde uit NwTag
(dit is dus nr 7 uit het voorbeeld)

3. Als Naam voorkomt in de dynamische tabel maar GEEN waardes heeft in kolommen Bedrag EN Mededelingen
dan de waarde uit kolom TAGS vervangen met de waarde uit NwTag
(dit is dus nr 5 uit het voorbeeld)

4. Als Mededelingen de exacte tekst BEVAT die voorkomt in dezelfde kolom in de dynamische tabel
dan de waarde uit kolom TAGS vervangen met de waarde uit NwTag
(dit is dus nr 6 uit het voorbeeld)

Als er niet voldaan wordt aan 1 van deze condities dan de waarde uit TAGS niet aanpassen.

Hopelijk kan ik hiermee geholpen worden, alvast dank!

 

[peter59]

Zie bijlage.
In je origineel komt SAM 2x in voor.
Hier heb ik ook een "ID-nr" aangegeven net zoals je aan b.v. Grietje hebt gedaan. Deze SAM komt nu in de output geen 4x meer voor.
Tevens staat in je origineel bij "Bron Data" >> "BlueEyes" en in "Dynamische Condities" >> "BleuEyes". Dit maar even gewijzigd.
En in het origineel bij "Gewenste output (TAGS aangepast obv condities)" ga je bij "Extraatje 2" ook de fout in.

Hopelijk komt het geheel in beetje in de buurt van zoals je het voor ogen hebt.

 

[Verwijderd lid 501906]

Frans: "Bleu" is het Franse woord voor "blauw".
Engels: "Blue" is het Engelse woord voor "blauw".

Ik was er allang achter om niet met fransozen samen te werken

Grapje

 

[Hela1966]

Hoi Peter,

Dank voor je reactie (en correcties qua spelling)

Het is echter niet mijn bedoeling om te ontdubbelen. Zo kan bijvoorbeeld de combinatie SAM en 3,00 meerdere keren per jaar voorkomen. Alle records daarvan dienen in de output te verschijnen maar dienen allemaal NwTag 7 te krijgen ongeacht wat er in de bron staat.
De letter 2 achter een naam is zomaar een verzinsel en geen identifier of zo.

Ik denk dat een Join niet echt zal werken voor mijn probleem.
Zou ik het moeten zoeken in Nested If Statements en dan iets van een loop of zo?
Ik heb ook al lopen puzzelen met List.Accumulate en find/replace (state, current) omdat ik dat ooit al eens gebruikt heb, maar loop () daar ook vast.

 

[emields]

ik vraag me enkel af hoe in gewenste output extratje2 aan 3 tags geraakt en SAM aan mijn studie?

 

[peter59]

@emields
Heb je mijn uitleg al gelezen in #2?
Dat heb ik al een beetje kenbaar gemaakt waar het fout gaat.
Dus wat TS nu vraagt in #4 komt absoluut niet overeen met het voorbeeld in #1.
M.a.w. is die bijlage in #1 absoluut niet representatief.
Let op: #3 kan je in de prullenbak gooien.

 

[JEC.]

Hier nog een voorbeeld via accumulate. Er wordt een extra kolom toegevoegd met de gewenste output.
tbl_rep is een list, te vinden in de bijlage.

let
    Source = Excel.CurrentWorkbook(){[Name="BronData"]}[Content],
    nTag = Table.AddColumn(Source, "TAG", (r)=>
        [
            vNaam = Record.Field(r,"Naam"),
            vBedrag = Record.Field(r,"Bedrag"),
            vMededeling = Record.Field(r,"Mededelingen"),
            vTag = Record.Field(r,"TAGS"),
            vResult =
                List.Accumulate(tbl_rep, vTag, (s,c)=>
                    if s = vTag then
                        if vNaam = c{1} and vBedrag = c{2} then c{4}
                        else if vNaam = c{1} and vMededeling= c{3} then c{4}
                        else if vNaam = c{1} and c{2} = "" and c{3} = "" then c{4}
                        else if Text.Contains(" " &vMededeling & " ", " " & c{3} & " ") then c{4} else vTag
                   else s                 
            )
        ][vResult])
in
    nTag

 

[JEC.]

Bovenstaande is beter leesbaar maar dit doet hetzelfde, gebruikmakend van Record.FieldValues

let
    Source = Excel.CurrentWorkbook(){[Name="BronData"]}[Content],
    nTag = Table.AddColumn(Source, "TAG", (r)=>
        [
            rValues = Record.FieldValues(r),
            vResult =
                List.Accumulate(tbl_rep, rValues{2}, (s,c)=>
                    if s = rValues{2} then
                        if rValues{0} = c{1} and rValues{1} = c{2} then c{4}
                        else if rValues{0} = c{1} and rValues{3}= c{3} then c{4}
                        else if rValues{0} = c{1} and c{2} = "" and c{3} = "" then c{4}
                        else if Text.Contains(" " &rValues{3} & " ", " " & c{3} & " ") then c{4} else rValues{2}
                   else s                     
            )
        ][vResult])
in
    nTag

 

[peter59]

@JEC.
De M-codes uit je post #7 en #8 werken perfect.
TS heeft hierbij een mooi Kerstcadeautje.

Wat betreft je codes, hoe doe je dat zo snel?
Heb je alle ongeveer 800 functies in je hoofd zitten en weet je direct wat ze doen?

Ik probeer me zelf altijd een beetje te helpen met deze link. Alleen moet je maar net weten waar je het dient te zoeken.
https://powerquery.how/
Voor de liefhebbers vind je hier een globale beschrijving van wat je kunt doen als je bovenstaande link opent.

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[JEC.]

Ik werk heel veel met Power Query. Uiteindelijk is alles een kwestie van veel doen, testen en lezen. En natuurlijk bijhouden.

Het evalueren van de functies binnen PQ gaat helaas niet zo simpel als in Excel functies of in VBA. Dus het kost meer tijd om de exacte werking te achterhalen.

 

[JEC.]

En je onthoudt zaken beter als je je ergens in interesseert natuurlijk!

 

[Hela1966]

Goeiemorgen JEC,

Wow, wat een geweldige oplossing! Zeker een mooi kerstcadeautje!
Het maakt me nog blijer dat ik "snap" wat er gedaan wordt en hoe ik deze code in de praktijk kan gaan toepassen.

Ook ik heb veel geleerd van Rick de Groot (BI Gorilla) en voornamelijk van veel oefenen, toepassen en niet opgeven (zeg maar vastbijten) als iets niet lukt. Mijn laatste resort is dan toch altijd het forum omdat ik er echt alles aan gedaan wil hebben om het zelf uit te vogelen.

Nogmaals excuses voor de inconsistenties in mijn voorbeeld en heel hartelijk bedankt voor je hulp.

Fijne Kerst gewenst van een happy PQ-gebruiker!

Groet,
Hela

 

[JEC.]

Mooizo! Graag gedaan

 

[snb]

In het bestand uit #7

Sub M_snb()
  sn = ListObjects(1).DataBodyRange
  sp = ListObjects(2).DataBodyRange
  
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
      If sn(j, 2) & sn(j, 3) <> "" Then .Item(sn(j, 2) & sn(j, 3)) = sn(j, 5)
      If sn(j, 2) & sn(j, 3) = "" Then .Item(sn(j, 4) & "_") = sn(j, 5)
    Next
    
    For j = 1 To UBound(sp)
      If .exists(sp(j, 1)) Then sp(j, 3) = .Item(sp(j, 1))
      If .exists(sp(j, 1) & sp(j, 2)) Then sp(j, 3) = .Item(sp(j, 1) & sp(j, 2))
      
      For Each it In Filter(.keys, "_")
        If InStr(sp(j, 4), Replace(it, "_", "")) Then sp(j, 3) = .Item(it)
      Next
    Next
  End With
  
  Cells(34, 1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub