Opgelost afronden in power query

[moensk]

in bijlage klein voorbeeld gemaakt
kolom "M³" komt uit ons TMS systeem.
kolom "Tar_m³" en "Tar_bedrag" moeten gezocht worden in een andere tabel.
als ik "Tar_m³" heb dan is bedrag bij zoeken vrij eenvoudig
de waarde in kolom "M³" moet naar boven afgerond worden in kolom "Tar_m³" naar de eerst volgende waarde dat hij tegen komt in de kolom "Tar_m³" in de tabel "Tarief_Info"

 

[JEC.]

De twee tabellen in je voorbeeld kwamen niet helemaal overeen.

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content]),
    lookupTbl = Table.Buffer( Excel.CurrentWorkbook(){[Name="Tarief_Info"]}[Content]),
    unPiv = Table.TransformColumnTypes(Table.UnpivotOtherColumns(lookupTbl, {"Tar_m³"}, "Attribute", "Tar_bedrag"),{{"Attribute", Int64.Type}}),
    merge = Table.NestedJoin(Source, {"Debiteur"}, unPiv, {"Attribute"}, "merge", JoinKind.LeftOuter),
    getData = Table.AddColumn(merge, "Custom", (r)=> Record.SelectFields(Table.SelectRows(r[merge], each [#"Tar_m³"] >= r[#"M³"]){0},{"Tar_m³","Tar_bedrag"})),
    expand = Table.ExpandRecordColumn(getData, "Custom", {"Tar_m³", "Tar_bedrag"}, {"Tar_m³", "Tar_bedrag"}),
    delCol = Table.RemoveColumns(expand,{"merge"}),
    result = Table.ReorderColumns(delCol,{"Opdrachtnr", "Debiteur", "Losplaats", "KG", "M³", "Tar_m³", "Tar_bedrag", "Tarief soort"})
in
    result

 

[peter59]

Iets anders aangevlogen d.m.v. een afrondingsfunctie.

 

[moensk]

thanks allebei
scripts peter59 kan ik logisch volgen die van JEC is wat complex voor mij om te volgen

 

[moensk]

JEC of peter59
kunnen dit even uitleggen, dit snap ik niet in script van JEC
rest kan ik allemaal volgen

r[#"M³"]){0}

 

[peter59]

Dit zegt ChatGPT er over.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    lookupTbl = Excel.CurrentWorkbook(){[Name="Tarief_Info"]}[Content],
    
    // Kolommen 'unpivoten' en de "Attribute"-kolom omzetten naar het Int64-type
    unPiv = Table.TransformColumnTypes(
        Table.UnpivotOtherColumns(lookupTbl, {"Tar_m³"}, "Attribute", "Tar_bedrag"),
        {{"Attribute", Int64.Type}}
    ),

    // De bron-tabel mergen met de 'unpivoted' lookup-tabel op basis van de "Debiteur"- en "Attribute"-kolommen
    merge = Table.NestedJoin(Source, {"Debiteur"}, unPiv, {"Attribute"}, "merge", JoinKind.LeftOuter),

    // Een aangepaste kolom toevoegen om de juiste Tar_m³ en Tar_bedrag te selecteren
    addTarief = Table.AddColumn(merge, "Tarief", each
        let
            tariefRow = Table.SelectRows([merge], each [#"Tar_m³"] >= [#"M³"]){0}
        in
            Record.SelectFields(tariefRow, {"Tar_m³", "Tar_bedrag"})
    ),

    // De "Tarief"-kolom uitbreiden om "Tar_m³" en "Tar_bedrag" toe te voegen
    expand = Table.ExpandRecordColumn(addTarief, "Tarief", {"Tar_m³", "Tar_bedrag"}, {"Tar_m³", "Tar_bedrag"}),

    // Kolommen herordenen om te voldoen aan de gewenste output
    result = Table.ReorderColumns(expand, {"Opdrachtnr", "Debiteur", "Losplaats", "KG", "M³", "Tar_m³", "Tar_bedrag", "Tarief soort"})
in
    result

 

[JEC.]

Met die “r” zorg je ervoor dat je verwijst naar de “hoofdtabel”, meegegeven in de table.addcolumn als tabel.

In de table.addcolumn zit dus een each en in de selectrows ook. Die (r)=> is eigenlijk een each welke je kan benaderen door een “r” voor de kolom te zetten. Dit is nodig omdat je anders twee keer each hebt. Ech kun je benaderen via “_”, maar dat kan dan voor beide functies gelden. Zo krijg je een error.

De {0} haalt de eerste regel van de tabel op

Beetje lastig uitleggen

 

[peter59]

@JEC.
Ja, dat blijft lastig uit te leggen.
Zelf zie ik de (r)=> als een nested let >> in
Zelf probeer ik zo veel als mogelijk gebruik te maken van de UserInterface.
Maar natuurlijk heeft ieder zijn eigen werkwijze.

 

[JEC.]

Binnen een let/in ken je waarden toe aan variabelen. De (r)=> is letterlijk “each”.

De functie gaat door iedere regel van de tabel en “r” is dan telkens gelijk aan die “record”

 

[moensk]

JEC - Peter69,
als bij opzoeken "Tar_m³" gevuld is doch kolommen erna zijn leeg dan werkt het niet dan zet hij m³ op 0 overal.
hoe kan dit aangepast worden dat hij wel altijd M³ vult ook als andere kolommen leeg zijn ?

 

[peter59]

@moensk
Ik kan je helaas even niet volgen.
Probeer in het voorbeeldje van @JEC. (#2) of van mij (#3) eens aan te geven wat je bedoelt.

 

[moensk]

excuses was niet zo duidelijk, gaat om script JEC.
in bijlage de excel zonder waardes en dan ziet ge dat hij leeg is in gewenste tabel

 

[JEC.]

Zo?

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content]),
    lookupTbl = Table.Buffer( Excel.CurrentWorkbook(){[Name="Tarief_Info"]}[Content]),
    repNulls = Table.ReplaceValue(lookupTbl,null,0,Replacer.ReplaceValue,{"10500", "50500", "50501", "50503", "50508"}),
    unPiv = Table.TransformColumnTypes(Table.UnpivotOtherColumns(repNulls, {"Tar_m³"}, "Attribute", "Tar_bedrag"),{{"Attribute", Int64.Type}}),
    merge = Table.NestedJoin(Source, {"Debiteur"}, unPiv, {"Attribute"}, "merge", JoinKind.LeftOuter),
    getData = Table.AddColumn(merge, "Custom", (r)=> Record.SelectFields(Table.SelectRows(r[merge], each [#"Tar_m³"] >= r[#"M³"]){0},{"Tar_m³","Tar_bedrag"})),
    expand = Table.ExpandRecordColumn(getData, "Custom", {"Tar_m³", "Tar_bedrag"}, {"Tar_m³", "Tar_bedrag"}),
    delCol = Table.RemoveColumns(expand,{"merge"}),
    result = Table.ReorderColumns(delCol,{"Opdrachtnr", "Debiteur", "Losplaats", "KG", "M³", "Tar_m³", "Tar_bedrag", "Tarief soort"})
in
    result

 

[moensk]

yep dat bedoelde ik, thanks

 

[moensk]

JEC
duidelijk, hoe kan volgende regel aangepast worden zodat als ik bij de tabel een kolom toevoeg bv "50504" deze automatisch hierbij komt ?

repNulls = Table.ReplaceValue(lookupTbl,null,0,Replacer.ReplaceValue,{"10500", "50500", "50501", "50503", "50508"}),

 

[JEC.]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content]),
    lookupTbl = Table.Buffer( Excel.CurrentWorkbook(){[Name="Tarief_Info"]}[Content]),
    colNames = List.Skip( Table.ColumnNames(lookupTbl),1),
    repNulls = Table.ReplaceValue(lookupTbl,null,0,Replacer.ReplaceValue,colNames),
    unPiv = Table.TransformColumnTypes(Table.UnpivotOtherColumns(repNulls, {"Tar_m³"}, "Attribute", "Tar_bedrag"),{{"Attribute", Int64.Type}}),
    merge = Table.NestedJoin(Source, {"Debiteur"}, unPiv, {"Attribute"}, "merge", JoinKind.LeftOuter),
    getData = Table.AddColumn(merge, "Custom", (r)=> Record.SelectFields(Table.SelectRows(r[merge], each [#"Tar_m³"] >= r[#"M³"]){0},{"Tar_m³","Tar_bedrag"})),
    expand = Table.ExpandRecordColumn(getData, "Custom", {"Tar_m³", "Tar_bedrag"}, {"Tar_m³", "Tar_bedrag"}),
    delCol = Table.RemoveColumns(expand,{"merge"}),
    result = Table.ReorderColumns(delCol,{"Opdrachtnr", "Debiteur", "Losplaats", "KG", "M³", "Tar_m³", "Tar_bedrag", "Tarief soort"})
in
    result

 

[moensk]

thanks voor snelle reactie en oplossing

 

[peter59]

Ook nog een duit in het zakje met zoveel als mogelijk met de UI van power query.

 

[moensk]

peter59, thanks