Új hozzászólás Aktív témák

• #51366 Mutt senior tag

  Új Válasz 2023-09-01 18:35:55 #51366

  Új hozzászólás

  Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra

  Privát üzenet küldése

  

  Mutt

  senior tag

  Sziasztok,

  51352-es kérdésben rangsor és korreláció számításhoz adtunk Excel függvényes megoldást, de közben készült egy Power Query (PQ) megoldás is.

  A probléma az, hogy PQ-ben nincs se korrelációs, se rang.átlag függvény. Hasonlók vannak, de nem ugyanazok. Természetesen lehet saját függvényeket létrehozni és most ezt szeretném bemutatni azoknak akiket érdekel.

  A saját függvény egy lekérdezés, csak úgy mint a többi, de itt nekünk kell az M-kódot (PQ nyelvezete) írni.
  A korreláció függvény kódja ez lett:

  //the formula is available on https://support.microsoft.com/en-US/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
(numbers1 as list, numbers2 as list) as number =>
let
avarage1 = List.Average(numbers1),
avarage2 = List.Average(numbers2),
x1 = List.Transform(numbers1, each _ - avarage1),
y1 = List.Transform(numbers2, each _ - avarage2),
x = List.Zip({x1, y1}),
nominator = List.Sum(List.Transform(x, each _{0} * _{1})),
denominator = Number.Sqrt(List.Sum(List.Transform(x, each _{0} * _{0})) * List.Sum(List.Transform(x, each _{1} *_ {1}))),
result = nominator / denominator
in
result

  Az elején megadjuk hogy milyen inputokat fogad el a függvény, majd jönnek a számítási lépések (átlagot számolunk, majd eltérést az átlagtól, majd négyzetes eltérést és a végén már csak osztanunk kell.
  Többször használtam a List.Transform iterátort, amely ahogy végig megy a lista elemein kiszámolja az értékeket a transzformációs függvény alapján.

  Használata utána már olyan egyszerű, mint az Excel függvényé.
  

  Rangsorhoz van beépített megoldás (Table.AddRankColumn), de nem úgy működik mint a RANG.ÁTL, így ezt is inkább megcsináltam függvénnyel. Ennek egy kicsit csúnyább lett a kódja, mert próbáltam meg dinamikusra csinálni illetve hogy egyszerre számolja ki a rangsort két különoszlopra.

  (input as table, sort1 as text, sort2 as text) as table =>
let
Source = input,
//memoize the original columns for rearrange
OriginalColumnOrder = Table.ColumnNames(Source),
//add index to be able to restore original
AddIndex = Table.AddIndexColumn(Source, "RowID", 1, 1, Int64.Type),
//sort the table by given criteria
SortBy1 = Table.Sort(AddIndex, {sort1, Order.Descending}),
//add ranking column
AddRankIndex1 = Table.AddIndexColumn(SortBy1, "Rank", 1, 1, Int64.Type),
//calculate the average rank (as excels RANK.AVG function)
GroupBy1 = Table.Group(AddRankIndex1, sort1, {
{Text.Combine({"Rank ", sort1}), each List.Average([Rank])},
{"Data", each _, type table}
}),
//get the list of the columns what we need for the next step
Columns1 = List.RemoveItems(Table.ColumnNames(GroupBy1[Data]{0}), {sort1, "Rank"}),
//by expanding the columns we are
Expand1 = Table.ExpandTableColumn(GroupBy1, "Data", Columns1),
//do the same things for the other column
SortBy2 = Table.Sort(Expand1, {sort2, Order.Descending}),
AddRankIndex2 = Table.AddIndexColumn(SortBy2, "Rank", 1, 1, Int64.Type),
GroupBy2 = Table.Group(AddRankIndex2, sort2, {
{Text.Combine({"Rank ", sort2}), each List.Average([Rank])},
{"Data", each _, type table}
}),
Columns2 = List.RemoveItems(Table.ColumnNames(GroupBy2[Data]{0}), {sort2, "Rank"}),
Expand2 = Table.ExpandTableColumn(GroupBy2, "Data", Columns2),
//restore the table to original sequence
OriginalSort = Table.Sort(Expand2, "RowID"),
RemoveExtraColumn = Table.RemoveColumns(OriginalSort, "RowID", MissingField.Ignore),
//reorder the columns to original logic
ReOrderColumns = Table.ReorderColumns(RemoveExtraColumn, List.Combine({OriginalColumnOrder, {Text.Combine({"Rank ", sort1})}, {Text.Combine({"Rank ", sort2})}}))
in
ReOrderColumns

  Itt ahogy látszik egy adattáblát adunk át a függvénynek és a két oszlop nevét, amelyekre sorrendet kell meghatározni. A sorrend meghatározás 4 lépésből áll:
  1. csökkenő érték szerint sorba rendezzük a táblát
  2. hozzáadunk egy sorrend (Rank) oszlopot a táblához.
  3. csoportosítjuk érték szerint az adatokat és a sorrend átlagát számoljuk ki közben.
  4. kibontjuk a csoportosítot adatokat.

  Ezt pedig így lehet használni.
  

  üdv

Új hozzászólás Aktív témák