# Power Query Challenge Problem

All,

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

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
4 Likes

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
``````

4 Likes

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

1. I used Group by to group the data by the “Category” column and create a subindex column.
2. Created an Index column starting from 1
3. 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]}}),
#"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

4 Likes

Hi @BrianJ

Thanks for challenge.

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

``````let
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),
#"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
``````
2 Likes

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

Thanks!

• Brian
1 Like

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

• Brian
1 Like

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]}}),
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]}}),
ExpandColumns = Table.ExpandTableColumn(AddedFirstIndex, "AddedFirstIndex", {"Category", "Value", "Index"}, {"Category", "Value", "Index"}),
AddedSecondIndex = Table.AddIndexColumn(ExpandColumns, "Index1", 0, 1, Int64.Type),
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]),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Custom", "Previous Value"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Previous Value", Int64.Type}})
in
ChangedType
``````
2 Likes

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

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
``````

Have fun!

2 Likes

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

1 Like

@Melissa Great Introduction but where is the solution?

1 Like

You’ll have to read some M code @MudassirAli

2 Likes

Hi @BrianJ,

This was fun, thank you

Here is my solution and details

Untitled.pbix (27.6 KB)

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

Best regards,
Joaly

2 Likes

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.

1 Like