Power Query Challenge Problem

BrianJ · August 27, 2021, 10:11pm

All,

Here’s a fun mid-level Power Query challenge problem from Chandeep Chhabra of Goodly.

Download Data - https://www.goodly.co.in/wp-content/uploads/2021/08/Data.xlsx
Post your answers here - https://www.goodly.co.in/refer-preview-row-subgroup-power-query/

If you solve this one, please take a couple of minutes to post your solution on Chandeep’s blog above in addition to below. He has been very generous about promoting Enterprise DNA content online, and it would be nice for us to do the same, given that he also produces very high quality content.

Thanks!

Brian

BrianJ · August 27, 2021, 10:16pm

Here’s my solution:

Did a group by all rows on category, added a table index, then two subindexes, one starting with 0 and the other starting with 1. Then did a self join based on table index and subindexes, and finally some cleanup:

let
Source = #"Raw Data",
#"Grouped Rows" = Table.Group(Source, {"Category"}, {{"AllData", each _, type table [Category=nullable text, Value=nullable number, Index=number]}}),
#"Added Table Index" = Table.AddIndexColumn(#"Grouped Rows", "Table Index", 1, 1, Int64.Type),
#"Added Subcat Index0" = Table.AddColumn(#"Added Table Index", "Custom", each Table.AddIndexColumn( [AllData], "Index0", 0, 1 )),
#"Removed Columns" = Table.RemoveColumns(#"Added Subcat Index0",{"Category", "AllData"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Category", "Value", "Index0"}, {"Category", "Value", "Index0"}),
#"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Category"}, {{"AllData", each _, type table [Table Index=number, Category=text, Value=number, Index0=number]}}),
#"Added Subcat Index1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn( [AllData], "Index1", 1, 1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Subcat Index1",{"Category", "AllData"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Table Index", "Category", "Value", "Index0", "Index1"}, {"Table Index", "Category", "Value", "Index0", "Index1"}),
#"Self Join on Indexes" = Table.NestedJoin(#"Expanded Custom1", {"Table Index", "Index0"}, #"Expanded Custom1", {"Table Index", "Index1"}, "Expanded Custom1", JoinKind.LeftOuter),
#"Expanded Expanded Custom1" = Table.ExpandTableColumn(#"Self Join on Indexes", "Expanded Custom1", {"Value"}, {"Value.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Expanded Custom1",{{"Category", Order.Ascending}, {"Index0", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Category", "Value", "Value.1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Category", type text}, {"Value", Int64.Type}, {"Value.1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Previous Value"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Sort", 1, 1, Int64.Type)
in
#"Added Index"

Brian
Brian Julius Solution2 to Goodly Power Query Challenge - August 27 2021.pbix (23.9 KB)

sedhosen · August 28, 2021, 8:02am

Hi @BrianJ
Thanks for bringing this challenge up.
Here is my solution:

I used Group by to group the data by the “Category” column and create a subindex column.
Created an Index column starting from 1
Created a custom column using if then else statement utilizing {[CategoryIndex]-2}[Value]) statement for returning the previous row from the “Value” column.

let
    Source = Excel.Workbook(File.Contents("C:\Users\superuser\Desktop\Power Query Challenge Data.xlsx"), null, true),
    Data_Table = Source{[Item="Data",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Table,{{"Category", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"GroupBy", each _, type table [Category=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TableIndex", each Table.AddIndexColumn([GroupBy],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"TableIndex"}),
    #"Expanded TableIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "TableIndex", {"Category", "Value", "Index"}, {"Category", "Value", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TableIndex",{{"Category", type text}, {"Value", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index.1", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index.1", "CategoryIndex"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Previous Value", each if [Index]=1 then null else #"Renamed Columns"{[CategoryIndex]-2}[Value]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Previous Value", Int64.Type}})
in
    #"Changed Type2"

Power Query Challenge - Hossein.pbix (18.3 KB)

Hossein

jbressan · August 28, 2021, 1:35pm

Hi @BrianJ

Thanks for challenge.

My approach was create a function the return a table with the requirement match, Current and Previous value.

let
    Source = Excel.Workbook(File.Contents("C:\Users\DellJose\Downloads\Data.xlsx"), null, true),
    Data_Table = Source{[Item="Data",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Table,{{"Category", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"AllRows", each _, type table [Category=nullable text, Value=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnPreviousValue", each fnPreviousValue([AllRows])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Category", "AllRows"}),
    #"Expanded fnPreviousValue" = Table.ExpandTableColumn(#"Removed Columns", "fnPreviousValue", {"Category", "Value", "Prior"}, {"Category", "Value", "Prior"})
in
    #"Expanded fnPreviousValue"

 let fnPreviousValue = (Data as table) as table =>
    let
        Source = Data,
        #"Added Index" = Table.AddIndexColumn(Source, "IndexA", 0, 1, Int64.Type),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "IndexB", 1, 1, Int64.Type),
        Custom1 = Table.NestedJoin( #"Added Index1",{"IndexA"},#"Added Index1",{"IndexB"},"Data",JoinKind.LeftOuter),
        #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Value"}, {"Data.Value"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"IndexA", Order.Ascending}}),
        #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Data.Value", "Prior"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"IndexA", "IndexB"})
    in
        #"Removed Columns"
in 
    fnPreviousValue

BrianJ · August 28, 2021, 1:55pm

@jbressan ,

Excellent solutions by both you and @sedhosen. Would you mind also posting yours on

Thanks!

Brian

sedhosen · August 28, 2021, 5:49pm

Already done @BrianJ

BrianJ · August 28, 2021, 6:25pm

@sedhosen ,

Thanks! - I saw your up there, was just asking Jose to post his. Super interesting how different all the solutions are.

Brian

MudassirAli · August 29, 2021, 1:06am

Thanks @Brian for the challenge.

Here is my solution.

1. Using Two Way Lookup

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    GroupedRows = Table.Group(Source, {"Category"}, {{"Count", each _, type table [Category=text, Value=number]}}),
    AddedCustom = Table.RemoveColumns(Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Count],"Index",0,1)),{"Category","Count"}),
    ExpandedCustom = Table.ExpandTableColumn(AddedCustom, "Custom", {"Category", "Value", "Index"}, {"Category", "Value", "Index"}),
    AddedIndex = Table.AddIndexColumn(ExpandedCustom, "Index.1", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each if [Index] = 0 then null else AddedIndex{[Index.1]-1}[Value]),
    RemovedColumns = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Custom", "Previous Value"}})
in
    RenamedColumns

2. Using Self Join

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    GroupedRows = Table.Group(Source, {"Category"}, {{"Count", each _, type table [Category=text, Value=number]}}),
    AddedFirstIndex = Table.SelectColumns(Table.AddColumn(GroupedRows, "AddedFirstIndex", each Table.AddIndexColumn([Count],"Index",0,1)),{"AddedFirstIndex"}),
    ExpandColumns = Table.ExpandTableColumn(AddedFirstIndex, "AddedFirstIndex", {"Category", "Value", "Index"}, {"Category", "Value", "Index"}),
    AddedSecondIndex = Table.AddIndexColumn(ExpandColumns, "Index1", 0, 1, Int64.Type),
    AddedThirdIndex = Table.AddIndexColumn(AddedSecondIndex, "Index.1", 1, 1, Int64.Type),
    MergeQueries = Table.NestedJoin(AddedThirdIndex, {"Index1"}, AddedThirdIndex, {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    MultipleCodes = Table.TransformColumnTypes(Table.RenameColumns(Table.RemoveColumns(Table.Sort(Table.ExpandTableColumn(MergeQueries, "Added Index1", {"Value"}, {"Value.1"}),{{"Index.1", Order.Ascending}}),{"Index1","Index.1"}),{"Value.1","Unadjusted Value"}),{{"Category",type text},{"Value", Int64.Type},{"Unadjusted Value", Int64.Type}}),
    AddedCustom = Table.AddColumn(MultipleCodes, "Custom", each if [Index] = 0 then null else [Unadjusted Value]),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"Unadjusted Value", "Index"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Custom", "Previous Value"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Previous Value", Int64.Type}})
in
    ChangedType

Power Query Challenge - Refer Preview Row for Subgroup.xlsx (46.2 KB)

sedhosen · August 29, 2021, 6:08am

Exactly!
I really impressed by the various ways to do so.

Melissa · August 29, 2021, 7:21am

So great to see each approach taken to this challenge
Here’s my take on it.

View Solution

Personal goal; create a solution without using “Group By”

I copied the sample into the “Enter Date” window
Created 2 lists, one with the Category and one with the Values
Added an Index, used the lists to identify grouping, retrieve previous row values and cleaned up helper columns

You can copy the full script below into a new blank query

let
    ValList = List.Buffer( Source[Value] ),
    CatList = List.Buffer( Source[Category] ),
    Source = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MlWK1YGyDcwQbAsDBNvIEEkNhO0EFjdAsE2R2IYmSOIQtjOIbWaJYJsgsy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t]), {{"Value", Int64.Type}}),
    AddPrevVal = Table.RemoveColumns( Table.AddColumn( Table.AddIndexColumn( Source, "Index", 0, 1 ), "PrevVal", each try if [Category] = CatList{[Index]-1}? then ValList{[Index]-1}? else null otherwise null, type number ), "Index" )
in
    AddPrevVal

Have fun!

BrianJ · August 29, 2021, 7:51am

@Melissa ,

That solution is an elegant 4 line mic drop. Wow – very intuitive in retrospect. Looks like I need to watch your selection and projection course chapter one more time.

– Brian

MudassirAli · August 30, 2021, 1:11am

@Melissa Great Introduction but where is the solution?

Melissa · August 30, 2021, 10:09am

You’ll have to read some M code @MudassirAli

JBocher · August 30, 2021, 1:06pm

Hi @BrianJ,

This was fun, thank you

Here is my solution and details

Untitled.pbix (27.6 KB)

I duplicate the table
I create two Index Columns : one from 0, the other from 1
I merge both table on Category and Index columns (LeftOuterJoin with left table the one with Index from 0)
I expand data, sort, delete and rename columns
I disable the load from the second table

Best regards,
Joaly

EnterpriseDNA · September 8, 2021, 6:16am

Thanks for starting this discussion @Brian and to all contributors of this post. We appreciate the engagement initiated and value created by posts like this one.

Looking forward to another one.