A Good Intermediate Power Query Challenge

Power Query
1

All,

For those looking to practice your Power Query skills, Chandeep Chhabra of Goodly has posted an excellent Power Query Challenge on his site. The whole thing can be done using the UI - no m coding necessary.

It’s also a very practical problem dealing with cost allocation.

With intermediate PQ skills, I think this challenge could be done in well under an hour.

I’ve posted my solution on Chandeep’s blog, but there are some very slick and highly efficient solutions using some more advance techniques if you’re inclined toward custom M programming.

Enjoy.

  • Brian
4 Likes
2

Thanks @BrianJ for sharing .

image
image1424×449 37.7 KB

Expand Columns to Multiple Rows by Delimiter.pbix (30.7 KB)

3 Likes
3

@Rajesh ,

Wow! This is a super-elegant and efficient solution. Fascinating to see how you constructed this - particularly the Repeat step.

Makes my solution look like a caveman banging on Power Query with a rock… :smile:

Thanks for posting!

  • Brian
4

Well, here’s my very literal, UI-driven caveman solution to this problem attached below…

It may not be elegant, but it does get the job done.

Fascinating to see all the different ways people solved this problem on Chandeep’s blog. Good stuff!

image
image1673×1329 332 KB

Goodly Power Query Challenge – May 2022 - Brian Julius Solution.pbix (27.4 KB)

  • Brian
5
Summary

I went the caveman route, although I got my grouping wrong initially so checked your answer @BrianJ and it was the lack of the value column in my grouping that caused my issue.

Will try to create a more elegant version with some more M if I get the time.

Goodly DJ Answer.pbix (29.7 KB)

1 Like
6

@DavieJoe ,

That was a nice curveball he threw in there - a repeated category with two different values. I missed that the first time I ran through my PQ steps, and then saw my allocated cost didn’t add up to the total and figured out where the discrepancy came from.

Have you watched his solution video that dropped earlier today? An anonymous poster (@melissa? :smile:) submitted a crazy great solution that works regardless of how many new columns of data get added and what they get named. Masterful.

  • Brian
1 Like
7

I haven’t looked at any other solutions yet as I thought I’d have a bash trying a more elegant/M solution.

1 Like
8

@DavieJoe ,

Don’t look at @Rajesh 's then. It was so good it hurt my feelings… :joy:

  • Brian
1 Like
9

Hi @BrianJ,
Here is the solution I have found. It took me a little longer than the hour you had planned. :joy:

ExpandColumnstoMultipleRows
ExpandColumnstoMultipleRows1366×730 74.7 KB

Regards,

Expand Columns to Multiple Rows_JAFP.pbix (65.7 KB)

2 Likes
10

Hi @Rajesh,
Very good solution with the calculation of the number of times each Cost Item value is going to be repeated by means of the Cartesian product of the 3 columns.
You could have used the comma character followed by a blank space as a separator so that it does not leave blank spaces at the beginning of the values.
Is the last step of changing the type of the columns to String necessary?

Regards,

11 
let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "rZHPasMwDMZfReSsQ1boA6RZRks2NpqWHUIPonGCWSpn/rOtbz/bDbRkoacdDLIl6/vpU10nFfXCQLF9SzB5wMUyxTRNfbzhRhLCvvSnyhAyZ/zrTlMjuUNYC/o6Q/Hp5HASbH0qNkJ4kkx8FAivg0kOWCe5cmz1Gd5lI8D/l+wb+PplOpWqMn/JFRvX2ygyqs00X29j75UmDhVQ/AyCjZjMEMH3pY8muAgvxK6lo3X6RuAu72LKe3Hkyhsyu+vcK6U+oBRiCHyVau036Qg4M/fF5/sWI9xaM+UPyqM7Uf1RdtJSD3lPxmilTgY23GoKg8xs+T8Q/u4IIWA9i4760ZfDLw==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                #"Cost Item" = _t,
                Value = _t,
                Location = _t,
                Business = _t,
                Department = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Cost Item", type text },
            { "Value", Int64.Type },
            { "Location", type text },
            { "Business", type text },
            { "Department", type text }
        }
    ),
    Crossjoin = Table.AddColumn (
        ChangedType,
        "Test",
        each
            let
                CurrentRow = _,
                SplitDepartment = Text.Split ( CurrentRow[Department], ", " ),
                SplitLocation = Text.Split ( CurrentRow[Location], ", " ),
                SplitBusiness = Text.Split ( CurrentRow[Business], ", " ),
                LocationListToTable = Table.RenameColumns (
                    Table.FromList ( SplitLocation ),
                    { "Column1", "Location" }
                ),
                AddBusiness = Table.AddColumn (
                    LocationListToTable,
                    "Business",
                    each SplitBusiness
                ),
                AddDepartment = Table.AddColumn ( AddBusiness, "Department", each SplitDepartment ),
                ExpandLocation = Table.ExpandListColumn ( AddDepartment, "Business" ),
                ExpandBusiness = Table.ExpandListColumn ( ExpandLocation, "Department" ),
                RowCount = Table.RowCount ( ExpandBusiness ),
                AddAllocation = Table.AddColumn (
                    ExpandBusiness,
                    "Allocated Amount",
                    each CurrentRow[Value] / RowCount
                )
            in
                AddAllocation
    ),
    RemoveOtherCols = Table.SelectColumns ( Crossjoin, { "Cost Item", "Test" } ),
    ExpandTest = Table.ExpandTableColumn (
        RemoveOtherCols,
        "Test",
        { "Location", "Department", "Business", "Allocated Amount" },
        { "Location", "Department", "Business", "Allocated Amount" }
    ),
    ChangedType2 = Table.TransformColumnTypes (
        ExpandTest,
        {
            { "Cost Item", type text },
            { "Department", type text },
            { "Location", type text },
            { "Business", type text },
            { "Allocated Amount", type number }
        }
    )
in
    ChangedType2
3 Likes
12

Hi @BrianJ :slight_smile: Thank you for sharing this Power Query Challenge, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others.

For now, we’re marking this post as solved due to inactivity. Feel free to reopen the thread if there’s anything else you want to add within the topic.