Power Query M - Group by Sloupec Hodnotu s Vlastní Agregace (Percentil)

Question 1

Snažím se vypočítat percentily podle skupiny (od sloupce hodnot ex: hodiny podle oddělení, prodej podle regionu, atd.) v power query. To stejné logiky by mohl být použit pro další vlastní skupiny agregace. Po mnoha hledání, našel jsem 2 potenciální přístupy.

Přístup 1:

tento archivovaný článek , který vypadal, že mají perfektní odpověď. Nic jiného jsem nemohl najít, přichází blízko.

Řešení je následující uživatelské funkce:

//PercentileInclusive Function
(inputSeries as list, percentile as number) =>
let
    SeriesCount = List.Count(inputSeries),
    PercentileRank = percentile * (SeriesCount - 1) + 1, //percentile value between 0 and 1
    PercentileRankRoundedUp = Number.RoundUp(PercentileRank),
    PercentileRankRoundedDown = Number.RoundDown(PercentileRank),
    Percentile1 = List.Max(List.MinN(inputSeries, PercentileRankRoundedDown)),
    Percentile2 = List.Max(List.MinN(inputSeries, PercentileRankRoundedUp)),
    PercentileInclusive = Percentile1 + (Percentile2 - Percentile1) * (PercentileRank - PercentileRankRoundedDown)
in
    PercentileInclusive

V kombinaci s krok ve své tabulce skupiny a vhodně používat funkce:

=Table.Group(TableName, {"Grouping Column"}, {{"New Column name", each
    PercentileInclusive(TableName[Column to calculate Percentile of], percentile # between 0 and 1)}})

[editovat opravit překlep Ron. R. poukázal na to, a odstranit zbytečné detail]

Příklad vstup:

Pero Typ	Prodaných Kusů
Ball-Point	6,109
Ball-Point	3,085
Ball-Point	1,970
Ball-Point	Nabídce 8 190
Ball-Point	6,006
Ball-Point	2,671
Ball-Point	6,875
Válec	778
Válec	9,329
Válec	7,781
Válec	4,182
Válec	2,016
Válec	5,785
Válec	1,411

Požadovaný výstup pro 25% včetně percentilu seskupeny podle Typ Pera:

Pero Typ	0.25 Inclusive Percentil (Správně)
Ball-Point	2,878
Válec	1,714

Poznámky: Ne počet desetinných míst je uvedeno výše, počítá s aplikace Excel.PERCENTILU.INC funkce.

Přístup 1 funguje skvěle.

Přístup 2: Zde je alternativní Power Query řešení jsem se snažil. To je jediný krok bez vlastní funkci. Zdá se, jako by to mělo stačit, ale nemůžu přijít na způsob, jak dělat podmíněné kontroly řadě založena. Něco musí jít tam, kde jsem //Stav//, která říká, které řádky patří v současné skupiny řádků, ale bez ohledu na to, co se snažím, to nefunguje. To buď zlomí, nebo dává percentil za vše, ignorování seskupení.

=List.Percentile(Table.Column(Table.SelectRows(#"Previous Step Name", //Condition//), "Column to calculate percentile of"), percentile # 0 to 1)

Nějaké nápady, jak udělat přístup 2 práce?

Question 2

Zdá se, že vaše Table.Group funkce je nesprávně uvedeno.

Kde můj předchozí krok byl #"Changed Type"následující práce:

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"Percentile", each fnPercentileINC([Units Sold],0.25)}})

Původní Data

M Kód

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pen Type", type text}, {"Units Sold", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"Percentile", each fnPercentileINC([Units Sold],0.25), type number}})
in
    #"Grouped Rows"

Výsledek

Edit: Pro váš přístup #2, bez vlastní funkci, můžete jednoduše použít List.Percentile jako agregace v Table.Group funkce:

   #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"25th Percentile", each List.Percentile([Units Sold],0.25)}       
        })

Ron Rosenfeld · Answer 1 · 2021-11-22T16:38:36

Zdá se, že vaše Table.Group funkce je nesprávně uvedeno.

Kde můj předchozí krok byl #"Changed Type"následující práce:

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"Percentile", each fnPercentileINC([Units Sold],0.25)}})

Původní Data

M Kód

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pen Type", type text}, {"Units Sold", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"Percentile", each fnPercentileINC([Units Sold],0.25), type number}})
in
    #"Grouped Rows"

Výsledek

Edit: Pro váš přístup #2, bez vlastní funkci, můžete jednoduše použít List.Percentile jako agregace v Table.Group funkce:

   #"Grouped Rows" = Table.Group(#"Changed Type", {"Pen Type"}, {
        {"25th Percentile", each List.Percentile([Units Sold],0.25)}       
        })

Díky za double-check. To byl opravdu hloupý překlep z mé strany, že jsem pořád dělal pokaždé, když jsem se odhlásil. Chystám se vyčistit původní otázku pro budoucí použití. Nějaké nápady, jak udělat přístup #2 práce?
@plasmas222 Viz můj edit. Stačí použít List.Percentile přímo v Table.Group agregace.
Fantastické, děkuji. Pro každého, odkazování, použití 2. přístup pokud je již postavena-in funkce, a používat první, když budete potřebovat vlastní funkci.

Power Query M - Group by Sloupec Hodnotu s Vlastní Agregace (Percentil)

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii