How to return the right most value in a row using Power Query

[Skip2MiLu]

Hi Guys

I struggling to find an appropriate M function or combination thereof that will return the value of the right most cell in a row that that contains a value.

I am able to do this in excel using the following functions, but not sure how to replicate this in power query.

Any help would be appreciate.

How to get right-most value of row in Excel

 

[johnny51981]

The best way that I have come up in PQs that I have created is to create a Custom Column that is a series of If statements. So there may be a better route to take,

It would look a little something like this:

=if [Column 3] <> null then [Column 3] else if[Column 2] <> null then [Column 2] else if [Column 1] <> null then [Column 1] else null

 

[RoryA]

If your columns are like the example, you could unpivot those and filter out nulls from the value column. The do a group query to get the maximum year for each id (if the headers are not numeric, you could add an index column, and get the max value of that), merge that query with the original so you only get the latest data, then repivot.

You could also unpivot, fill down, then just filter for the last column header, and merge that with the original data. I imagine you could do something with List.Transform too.

 

[GraH]

The idea in PQ could be to make a list of each record, remove all nulls and use List.Last on that list.
On my smartphone now, so I'm not able to provide the correct syntax, so I'm hoping the small guidelines are enough.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex,"Col", each List.Last(List.RemoveNulls(Record.ToList(Source{[Index]})))),
    OutputTable = Table.FromList(AddCustom[Col], Splitter.SplitByNothing(), {"Last Non-null"}, null, ExtraValues.Error)
in
    OutputTable

Last Non Null.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10		Last Non-null
2	106	80	86	43	45							45
3	7	58	120	143	51	130						130
4	69	99	140	76			88					88
5	141	129	70	142	150	27	123	111	28			28
6	12	137	74	2	61	11	105	83	30	5		5
7	102	125	138	93	95	39	115					115
8	37	90	63	29	55	18	16	49	128	119		119
9
10	40	131	88									88
11	134					4	14	52				52
12	19	25	133	127	110							110
13	139	67	101	77	17	41	42	64	1	124		124
14	144	53	10							111		111
15	35	108	9	145	89	104	103	65				65
16	81	122										122
17
Sheet4

 

[GraH]

Alternative without index, and the inner let .. in construction could be a nested formula, but I prefer this construction for longer term maintenance.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GetLast = Table.AddColumn(Source, "LastValue", each
          let
              Record = Source{_}
            , Values= Record.FieldValues(Record)
            , NoNulls = List.RemoveNulls(Values)
            , Last = List.Last(NoNulls)
        in
           Last
        )
in
    GetLast

Output:

Book1
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	LastValue
2	339			438	884		884
3	784	83	611	681	71		71
4	27	513	121	631	479	387	387
5	175		658			651	651
6	883	701	683	152			152
7	725	526					526
8	859						859
9	92	891	332	774	420	552	552
10			785			453	453
11	286	327	739	691	489	658	658
12	368	871		215		38	38
13	975		301		641		641
14		454					454
15		69	655				655
16			783	879			879
Sheet2

 

[Skip2MiLu]

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex,"Col", each List.Last(List.RemoveNulls(Record.ToList(Source{[Index]})))),
    OutputTable = Table.FromList(AddCustom[Col], Splitter.SplitByNothing(), {"Last Non-null"}, null, ExtraValues.Error)
in
    OutputTable

Last Non Null.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10		Last Non-null
2	106	80	86	43	45							45
3	7	58	120	143	51	130						130
4	69	99	140	76			88					88
5	141	129	70	142	150	27	123	111	28			28
6	12	137	74	2	61	11	105	83	30	5		5
7	102	125	138	93	95	39	115					115
8	37	90	63	29	55	18	16	49	128	119		119
9
10	40	131	88									88
11	134					4	14	52				52
12	19	25	133	127	110							110
13	139	67	101	77	17	41	42	64	1	124		124
14	144	53	10							111		111
15	35	108	9	145	89	104	103	65				65
16	81	122										122
17
Sheet4

Thanks for all the support. Still a bit new to M language and power query.

So let me use your code to create a custom column and see if works for my use case.

Will test is out this weekend and give feedback, but I looks like all is in order.

Just to give a bit of context on my scenario.

So I want to create 2x customer column using the below data:

1. Average Price = 2021, 2022 Amt (Column AT) / 2021, 2022 Qty (Column AC)
2. Latest Price = Right Most Amt (Column AH:AS) / Right Most Qty (Column Q:AB)
   
   -Also need to zero or null any calculations on rows where there is a 0 or null Amt being divided by Qty with a value (This is in the case of qty samples being shipped without a value amt as in row 7 as an example)
   
   
   

 

[Skip2MiLu]

Alternative without index, and the inner let .. in construction could be a nested formula, but I prefer this construction for longer term maintenance.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GetLast = Table.AddColumn(Source, "LastValue", each
          let
              Record = Source{_}
            , Values= Record.FieldValues(Record)
            , NoNulls = List.RemoveNulls(Values)
            , Last = List.Last(NoNulls)
        in
           Last
        )
in
    GetLast

Output:

Book1
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	LastValue
2	339			438	884		884
3	784	83	611	681	71		71
4	27	513	121	631	479	387	387
5	175		658			651	651
6	883	701	683	152			152
7	725	526					526
8	859						859
9	92	891	332	774	420	552	552
10			785			453	453
11	286	327	739	691	489	658	658
12	368	871		215		38	38
13	975		301		641		641
14		454					454
15		69	655				655
16			783	879			879
Sheet2

Excellent solution thanks!

 

[Skip2MiLu]

Excellent solution thanks!

View attachment 44721

Correction