Merging query on itself using value -1

[sprotson]

In my scenario I have a query which contains audit data - how an item has changed thorugh time. Each record in the query has an ID number, a version number and the value of various other columns of that version.

Simple example below of 5 versions of the same item

BCID	Version	Value
1	1	a
1	2	b
1	3	c
1	4	d
1	5	e

I want to be able to merge this query on itself in order to show the values of the previous version side by side, ie

BCID	Version	Value	Prev Version	Prev Value
1	1	a
1	2	b	1	a
1	3	c	2	b
1	4	d	3	c
1	5	e	4	d

So far I have taken a duplicate of the query and then I do a standard left merge on BCID=BCID and BCVersion=BCVersion.

I then adjust in the formula bar the join on BCVersion, as I want it to with be BCVersion=BCVersion-1, ie the previous version

= Table.NestedJoin(#"Application_Versions (2)", {"BCID", "BCVersion"}, #"Application_Versions (3)", {"BCID", "BCVersion"-1}, "Application_Versions (3)", JoinKind.LeftOuter)

Both the BCVersion and BCID columns are whole number.

I get the following error

Expression.Error: We cannot apply operator - to types Text and Number

Any thoughts how I can achieve my goal?

[FrankAT]

Hi @sprotson 

you can do it like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEpVgdCM8IiJPgPGMgTobzTIA4Bc4zBeJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BCID = _t, Version = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BCID", Int64.Type}, {"Version", Int64.Type}, {"Value", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Prev Version", each try #"Added Index"[Version]{[Index]-1} otherwise null ),
    Custom1 = Table.AddColumn(#"Added Custom", "Prev Value", each try #"Added Index"[Value]{[Index]-1} otherwise null ),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Prev Version", Int64.Type}, {"Prev Value", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
    #"Removed Columns"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[edhans · Accepted solution]

Hi @sprotson The 2nd and 4th parameters of the join function are column names, which are text. You cannot do any math on them to increment rows.

Take a look at this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEpVgdCM8IiJPgPGMgTobzTIA4Bc4zBeJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BCID = _t, Version = _t, Value = _t]),
    ShiftedTable = {null} & List.RemoveLastN(Table.Column(Source, "Version"), 1),
    OtherColumn = {null} & Table.SelectRows(Source, each List.Contains(ShiftedTable, [Version]))[Value],
    NestedLists = Table.ToColumns(Source) & {ShiftedTable} & {OtherColumn},
    BackToTable = Table.FromColumns(NestedLists, Table.ColumnNames(Source) & {"Previous Row"} & {"Previous Value"})
in
    BackToTable

This will take this table:

and convert to this table:

THis is based somewhat on Imke's article here, but hers goes into much more detail using a custom function. Mine assume you only need to compare the Value to Previous Value. You can add more columns in the transformation by adding lists in the NestedLists step and adding those column names in the final step.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

The other way to do this is to add two indexes to your table. The first starting with 1, then the 2nd starting with 0, then merge the table with itself using the  Index1 column in the first table with the Index0 in the 2nd table.

[sprotson]

Thanks

I understand that the merge formula detaisl the names of the columns, but surely the execution of the join is on the contents of those columns - so strange you could not adjust the formula in this way, as I could in other tools

For a novice like myself, I think the best approiach is going to be the indexes - easy to understand, although technically what it is going to do is exclude any item that is at version 1, as there is no previous version to match against.

I think ultimately, the best option is going to make sure I get the data in the correct format before it reaches Power BI. I can easily join a table on itself in sql as I am tryign to do here.

I was hopeful I could easily do it in Power BI, in case I am not using a sql db, but the formula you provided is way above my knowledge currently

I can apply it as a new source easily, but to be hinest, I am not sure how I would apply it in a real world example. ie how do I apply it to a query I already have and specifiy what columns should move down a row etc

Thanks your help anyway

[amitchandak]

@ImkeF , @edhans , can you help on this