Power Query Custom Column Formula

[KaiA]

Dear all,

If anyone knows how the following calculation can be done in Power Query M Code, that would be very much appreciated:

NAV	Diff
4733891,955	=WENNFEHLER(F5/F6-1;"")
4733891,955	=WENNFEHLER(F6/F7-1;"")
4733891,955	=WENNFEHLER(F7/F8-1;"")
4733891,955	=WENNFEHLER(F8/F9-1;"")
4394687,895	=WENNFEHLER(F9/F10-1;"")
4296264,18007194	=WENNFEHLER(F10/F11-1;"")
4394687,895	=WENNFEHLER(F11/F12-1;"")
4296264,18007194	=WENNFEHLER(F12/F13-1;"")
4394687,895	=WENNFEHLER(F13/F14-1;"")
4296264,18007194	=WENNFEHLER(F14/F15-1;"")
4394687,895	=WENNFEHLER(F15/F16-1;"")

This is just an example ("WENNFEHLER" means "iferror"). The table will have a column "NAV" with an arbitrary number of rows sorted by Date so that the most current date will always be in the first row. I would like that the custom column shows the percentage change from date to date (row to row). In this example the result would look like this:

NAV	Diff
4733891,955	0%
4733891,955	0%
4733891,955	0%
4733891,955	8%
4394687,895	2%
4296264,180	-2%
4394687,895	2%
4296264,180	-2%
4394687,895	2%
4296264,180	-2%
4394687,895

In general I have difficulties to understand the principle of this kind of calculation in M Code.

Many thanks,
Kai

[Aflatoon]

Power Query doesn't really do row context, but what you can do is add an index column, then load the column of values into a list that you can then access by position using the index column - 1 - for example:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    PreviousRows = List.Buffer(#"Added Index"[NAV]),
    Final = Table.AddColumn(#"Added Index", "Diff", each if [Index] > 0 then [NAV] / PreviousRows{[Index] - 1} - 1 else null, Percentage.Type)
in
    Final

[Aflatoon]

If you have a large dataset, you may get better performance by simply adding two index columns, one starting at 0 and the other starting at 1, then merge the query with itself joining the second index column to the first. You can then extract the previous row data from the resulting table.

[KaiA]

Dear Aflatoon,

Thank you very much for this. I tried to integrate your example in my code but I have not succeeded. Would you mind taking a look at my code? Maybe you have an idea how to make it work. Note: I had created an index column (step 4) in my code already. It does not need to be in step 4:

let
Quelle = Table.NestedJoin(Kurse, {"WP ID", "Kursdatum"}, Anteile, {"ISIN Intern", "Datum"}, "Anteile", JoinKind.LeftOuter),
#"Erweiterte Anteile" = Table.ExpandTableColumn(Quelle, "Anteile", {"Stueck/Nominal", "Kurs", "Kurs Dirty", "Marktwert Clean DW", "Marktwert Dirty DW"}, {"Emittenten.Exposure.Stueck/Nominal", "Emittenten.Exposure.Kurs", "Emittenten.Exposure.Kurs Dirty", "Emittenten.Exposure.Marktwert Clean DW", "Emittenten.Exposure.Marktwert Dirty DW"}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Erweiterte Anteile", "NAV", each if [Notierung] <> "Kurs" then [Kurs] * [#"Emittenten.Exposure.Stueck/Nominal"] else ([Kurs] / 100) * [#"Emittenten.Exposure.Stueck/Nominal"]),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Index", 0, 1, Int64.Type),
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Hinzugefügter Index", {"WP ID", "Kursdatum"}, Geschäfte, {"WP ID", "Handelstag"}, "Geschäfte", JoinKind.FullOuter),
#"Erweiterte Geschäfte" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Geschäfte", {"Gesch.-Nr.", "Gesch.-Art", "Nominal", "Kurs", "Handelstag", "Valuta", "Nominalbasis", "Betrag AW", "Aktueller Geschäfts- Status"}, {"Geschäfte.Gesch.-Nr.", "Geschäfte.Gesch.-Art", "Geschäfte.Nominal", "Geschäfte.Kurs", "Geschäfte.Handelstag", "Geschäfte.Valuta", "Geschäfte.Nominalbasis", "Geschäfte.Betrag AW", "Geschäfte.Aktueller Geschäfts- Status"}),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Erweiterte Geschäfte", "Freikommentar", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Freikommentar.1", "Freikommentar.2"}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Freikommentar.1", type text}, {"Freikommentar.2", type text}}),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Geänderter Typ", "Freikommentar", each if [Freikommentar.2] = null then [Freikommentar.1] else [Freikommentar.2]),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Hinzugefügte benutzerdefinierte Spalte1",{"WP ID", "WP-Name", "Kursart", "Kursdatum", "Kurs", "Freikommentar.1", "Freikommentar.2", "Freikommentar", "Datenquelle", "MBI", "Notierung", "Datum.Freikommentar", "Emittenten.Exposure.Stueck/Nominal", "Emittenten.Exposure.Kurs", "Emittenten.Exposure.Kurs Dirty", "Emittenten.Exposure.Marktwert Clean DW", "Emittenten.Exposure.Marktwert Dirty DW", "NAV", "Index", "Geschäfte.Gesch.-Nr.", "Geschäfte.Gesch.-Art", "Geschäfte.Nominal", "Geschäfte.Kurs", "Geschäfte.Handelstag", "Geschäfte.Valuta", "Geschäfte.Nominalbasis", "Geschäfte.Betrag AW", "Geschäfte.Aktueller Geschäfts- Status"}),
#"Andere entfernte Spalten" = Table.SelectColumns(#"Neu angeordnete Spalten",{"WP ID", "WP-Name", "Kursart", "Kursdatum", "Kurs", "Freikommentar", "Datenquelle", "MBI", "Notierung", "Datum.Freikommentar", "Emittenten.Exposure.Stueck/Nominal", "Emittenten.Exposure.Kurs", "Emittenten.Exposure.Kurs Dirty", "Emittenten.Exposure.Marktwert Clean DW", "Emittenten.Exposure.Marktwert Dirty DW", "NAV", "Index", "Geschäfte.Gesch.-Nr.", "Geschäfte.Gesch.-Art", "Geschäfte.Nominal", "Geschäfte.Kurs", "Geschäfte.Handelstag", "Geschäfte.Valuta", "Geschäfte.Nominalbasis", "Geschäfte.Betrag AW", "Geschäfte.Aktueller Geschäfts- Status"}),
#"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Andere entfernte Spalten", "Kursdatum.1", each if [Kursdatum] = null then [Geschäfte.Handelstag] else [Kursdatum]),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte2",{{"Kursdatum.1", type date}}),
#"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Geänderter Typ1",{"WP ID", "WP-Name", "Kursart", "Kursdatum", "Kursdatum.1", "Kurs", "Freikommentar", "Datenquelle", "MBI", "Notierung", "Datum.Freikommentar", "Emittenten.Exposure.Stueck/Nominal", "Emittenten.Exposure.Kurs", "Emittenten.Exposure.Kurs Dirty", "Emittenten.Exposure.Marktwert Clean DW", "Emittenten.Exposure.Marktwert Dirty DW", "NAV", "Index", "Geschäfte.Gesch.-Nr.", "Geschäfte.Gesch.-Art", "Geschäfte.Nominal", "Geschäfte.Kurs", "Geschäfte.Handelstag", "Geschäfte.Valuta", "Geschäfte.Nominalbasis", "Geschäfte.Betrag AW", "Geschäfte.Aktueller Geschäfts- Status"}),
#"Andere entfernte Spalten1" = Table.SelectColumns(#"Neu angeordnete Spalten1",{"WP ID", "WP-Name", "Kursart", "Kursdatum.1", "Kurs", "Freikommentar", "Datenquelle", "MBI", "Notierung", "Datum.Freikommentar", "Emittenten.Exposure.Stueck/Nominal", "Emittenten.Exposure.Kurs", "Emittenten.Exposure.Kurs Dirty", "Emittenten.Exposure.Marktwert Clean DW", "Emittenten.Exposure.Marktwert Dirty DW", "NAV", "Index", "Geschäfte.Gesch.-Nr.", "Geschäfte.Gesch.-Art", "Geschäfte.Nominal", "Geschäfte.Kurs", "Geschäfte.Handelstag", "Geschäfte.Valuta", "Geschäfte.Nominalbasis", "Geschäfte.Betrag AW", "Geschäfte.Aktueller Geschäfts- Status"}),
#"Sortierte Zeilen" = Table.Sort(#"Andere entfernte Spalten1",{{"Kursdatum.1", Order.Descending}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Sortierte Zeilen",{{"Kursdatum.1", "Datum"}})
in
#"Umbenannte Spalten"

Thank you very much for your support.

Kai

[Aflatoon]

It would make life a lot easier if you posted a sample workbook or at least gave an indication of what you actually tried that didn't work. Given that you have a sort operation in there, at what point are you intending to refer to the previous row?

[KaiA]

Dear Aflatoon,

I moved your code to the end of my code a second time (I tried before but must have done something wrong), and now it worked!

This is just great - many thanks, this is very much appreciated.

So if I rate this thread, this is how I rate your support, right?

Is there a way to modifie the code so that the calculation starts at the end of the table (oldest date)? The top of the table contains the most recent date. So what I need is actually the other way around. I just tried "then [NAV] / PreviousRows{[Index] + 1}" instead then [NAV] / PreviousRows{[Index] - 1} and it seems to work.

Thanks,
Kai

[Aflatoon]

That will work except you will need error handling for the last row now, rather than the first.