Power Query: Last Non Blank

[Mer333]

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:

[table="width: 500, class: grid"]
[tr]
[td]Product[/td]
[td]Class[/td]
[td]Region[/td]
[td]Week[/td]
[td]Price[/td]
[td]Sales[/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]40[/td]
[td]254[/td]
[td]777[/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]43[/td]
[td]256[/td]
[td]445[/td]
[/tr]
[tr]
[td]C[/td]
[td]Ships[/td]
[td]South America[/td]
[td]6[/td]
[td]356[/td]
[td]577[/td]
[/tr]
[tr]
[td]D[/td]
[td]Ships[/td]
[td]South America[/td]
[td]3[/td]
[td]345[/td]
[td]57[/td]
[/tr]
[tr]
[td]E[/td]
[td]Cars[/td]
[td]North America[/td]
[td]4[/td]
[td]4563[/td]
[td]5757[/td]
[/tr]
[tr]
[td]F[/td]
[td]Cars[/td]
[td]North America[/td]
[td]4[/td]
[td]665[/td]
[td]7777[/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]45[/td]
[td]234[/td]
[td]654[/td]
[/tr]
[/table]

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:

[table="width: 600, class: grid"]
[tr]
[td]Product[/td]
[td]Class[/td]
[td]Region[/td]
[td]Week[/td]
[td]Price[/td]
[td]Sales[/td]
[td]Sales last non blank[/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]40[/td]
[td]254[/td]
[td]777[/td]
[td][/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]43[/td]
[td]256[/td]
[td]445[/td]
[td]777[/td]
[/tr]
[tr]
[td]C[/td]
[td]Ships[/td]
[td]South America[/td]
[td]6[/td]
[td]356[/td]
[td]577[/td]
[td][/td]
[/tr]
[tr]
[td]D[/td]
[td]Ships[/td]
[td]South America[/td]
[td]3[/td]
[td]345[/td]
[td]57[/td]
[td][/td]
[/tr]
[tr]
[td]E[/td]
[td]Cars[/td]
[td]North America[/td]
[td]4[/td]
[td]4563[/td]
[td]5757[/td]
[td][/td]
[/tr]
[tr]
[td]F[/td]
[td]Cars[/td]
[td]North America[/td]
[td]4[/td]
[td]665[/td]
[td]7777[/td]
[td][/td]
[/tr]
[tr]
[td]A[/td]
[td]Bikes[/td]
[td]Africa[/td]
[td]45[/td]
[td]234[/td]
[td]654[/td]
[td]445[/td]
[/tr]
[/table]

 

[ImkeF]

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]

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]

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

 

[Mer333]

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]

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 )