# Power Query: Last Non Blank



## Mer333 (Feb 12, 2015)

Hi all! 

I came up against a problem where I have to get a value in Power Query for an equal row in the last non blank period.

For example:


ProductClassRegionWeekPriceSalesABikesAfrica40254777ABikesAfrica43256445CShipsSouth America6356577DShipsSouth America334557ECarsNorth America445635757FCarsNorth America46657777ABikesAfrica45234654

So what I have to do here is for each combination of Product + Class + Region find a sales amount for the last non blank week (so I have to make sure that week is exist and it has a sales).

Has anyone faced with something like this?

Expected result:


ProductClassRegionWeekPriceSalesSales last non blankABikesAfrica40254777ABikesAfrica43256445777CShipsSouth America6356577DShipsSouth America334557ECarsNorth America445635757FCarsNorth America46657777ABikesAfrica45234654445


----------



## ImkeF (Feb 12, 2015)

This works, but I wonder if there are special functions for it in the language.


```
let
    Quelle = Excel.CurrentWorkbook(){[Name="tValues"]}[Content],
    CreateKeyCode = Table.AddColumn(Quelle, "KeyCode", each [Product]&[Class]&[Region]),
    CreateSortCode = Table.AddColumn(CreateKeyCode, "SortCode", each [KeyCode]&Text.From([Week])),
    CreateSortIndex = Table.AddIndexColumn(CreateSortCode, "Index", 0, 1),
    Sort = Table.Sort(CreateSortIndex,{{"KeyCode", Order.Ascending}, {"Week", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(Sort, "Index.1", 2, 1),
    #"AddIndexColum-1" = Table.AddColumn(AddIndex, "IndexJoin", each [Index.1]-1),
    SelfJoinOnIndexColumns = Table.NestedJoin(#"AddIndexColum-1",{"KeyCode", "IndexJoin"},#"AddIndexColum-1",{"KeyCode", "Index.1"},"NewColumn"),
    ExpandSales = Table.ExpandTableColumn(SelfJoinOnIndexColumns, "NewColumn", {"Sales"}, {"NewColumn.Sales"}),
    RenameColumn = Table.RenameColumns(ExpandSales,{{"NewColumn.Sales", "SalesLastNonBlank"}}),
    SortBack = Table.Sort(RenameColumn,{{"Index", Order.Ascending}})
in
    SortBack
```


----------



## billszysz (Feb 12, 2015)

Hi Mer333,

You can try the code below if you want.
This is only fast answer, so i do not sure if it is the best solution.


```
let
    Zrodlo = Excel.CurrentWorkbook(){[Name="tblMain"]}[Content],
    AddConcatCol = Table.AddColumn(Zrodlo, "Concat", each Text.Combine({[Product], [Class], [Region]}, ":"), type text),
    AddedIndex1 = Table.AddIndexColumn(AddConcatCol, "Indeks", 0, 1),
    SortByConcatAndWeek = Table.Sort(AddedIndex1,{{"Concat", Order.Ascending}, {"Week", Order.Ascending}}),
    AddedIndex2 = Table.AddIndexColumn(SortByConcatAndWeek, "Indeks.1", 0, 1),
    TwoIf = Table.AddColumn(AddedIndex2, "Sales (LNB)", each if [Indeks.1]=0 then null else if AddedIndex2{[Indeks.1]-1}[Concat]=[Concat] then AddedIndex2{[Indeks.1]-1}[Sales] else null),
    SortToOrigin = Table.Sort(TwoIf,{{"Indeks", Order.Ascending}}),
    RemCol = Table.RemoveColumns(SortToOrigin,{"Concat", "Indeks", "Indeks.1"})
in
    RemCol
```


----------



## scottsen (Feb 13, 2015)

Wow, you dudes are PQ & M ninjas!  I would *totally* just do this in Power Pivot...


----------



## Mer333 (Feb 13, 2015)

Hi guys!!!

Thank you for your answers!

ImkeF - just amazing! Works fine even though it increase memory size twice by NestedJoin.

Bill - I also tried your solution based on your popular practice from YouTube but something went wrong... Memory size increased up to 20 times and then I switched it off.

Scottsen - I agree that in most common cases it should be solved by DAX but in my case actually I need the same not for sales but for price... If I don't have price in the current period I still need it from the previous one where it has ever existed. It's a matter of correct data so it's better to solve in PQ. I just replace price column by the new one. Everything you can solve in PQ better do there in purpose not to overload your data model and DAX formulas.


----------



## billszysz (Feb 14, 2015)

Hi Mer333,

As you can see, flu is not good for thinking 
You are absolutely right. My previous way is not good for this case.
Here is almost identical solution to *ImkeF*.
Almost because we do not need concatenate columns and it is "by clicking" way (suppose, it is not important to you)
This is original version from my PQ (automatic version)
First query name is like excel table name....second, the same (automatic named by PQ)
Table name in my sheet is "tblMain"

this is "tblMain" query code

```
let
    Źródło = Excel.CurrentWorkbook(){[Name="tblMain"]}[Content],
    #"Dodano indeks" = Table.AddIndexColumn(Źródło, "Indeks", 0, 1),
    #"Posortowano wiersze" = Table.Sort(#"Dodano indeks",{{"Product", Order.Ascending}, {"Class", Order.Ascending}, {"Region", Order.Ascending}, {"Week", Order.Ascending}}),
    #"Dodano indeks1" = Table.AddIndexColumn(#"Posortowano wiersze", "Indeks.1", 0, 1),
    #"Dodano indeks2" = Table.AddIndexColumn(#"Dodano indeks1", "Indeks.2", 1, 1)
in
    #"Dodano indeks2"
```

and this is "Merge1" query code

```
let
    Źródło = Table.NestedJoin(tblMain,{"Product", "Class", "Region", "Indeks.1"},tblMain,{"Product", "Class", "Region", "Indeks.2"},"Nowa kolumna"),
    #"Rozwiń Nowa kolumna" = Table.ExpandTableColumn(Źródło, "Nowa kolumna", {"Sales"}, {"Nowa kolumna.Sales"}),
    #"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Rozwiń Nowa kolumna",{{"Nowa kolumna.Sales", "Sales (LNB)"}}),
    #"Posortowano wiersze" = Table.Sort(#"Zmieniono nazwy kolumn",{{"Indeks", Order.Ascending}}),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Posortowano wiersze",{"Indeks", "Indeks.1", "Indeks.2"})
in
    #"Usunięto kolumny"
```

Regards )


----------

