Group by a column with expansion

[gerasimovav]

Hello coleages.

I have such table:

ClientID   Name   Surname  OrderNum   OrderDate    PartNum   Item Name   Count

134          Alex      Jones         45              21.12.2017    45345         T-Shirt          3

134          Alex      Jones         45              21.12.2017    7643           Cap               2

36           Sam       Grey           55             27.12.2017     5436          Sticker           6

36           Sam       Grey           55             27.12.2017     5498          Slicer             1

39           Michel   Salgado      42             28.12.2017     3342          Book             3

there are one order consist of a several items (Order 45 includes 3 T-Shirts and 2 Caps)

I have to get the table:

Order Num     ClientId               OrderDate                1-st item                    2-nd item       3-rd item           4-th item     5-th item

45                     134                     21.12.2017               45345 2 T-Shirt        7643 2 Cap       None                None           None

55                     36                        27.12.2017               5436 6 Sticker         5498 1 Slicer     None                None           None

42                     39                        28.12.2017               3342 3 Book            None                None                None           None

How can I done it?

[v-juanli-msft · Accepted solution]

Hi @gerasimovav

Steps below:

1.Add an index column based on “Count” column group by the “OrderNum” column.

2.Merge “PartNum”, “Count”, “Item Name” columns

3.Add conditional columns

4.Fill up null values

5.Remove Duplicates

You could click the icon on the APPLIED STEPS pane, then you can see how to set the steps.

Code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/D8IgEMW/imGujXDQP2N1MDFxqlvTgSCxpCgGO+i39ziWjrrce3nc7+UYBsZBsoJ13r5RTuFhX6hS4RC85KIUO15vKAJKL9t+cnFBB2wsfufrSgLKQT/TE6FQoe31Hecx2g+KIq5ec0rmtcWZ2UZ01T9s26Q17wyhPKMt2rMzk/XU4W/6GtLRIhU06wIACvchzPnD4xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Name = _t, Surname = _t, OrderNum = _t, OrderDate = _t, PartNum = _t, #"Item Name" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"OrderNum", Int64.Type}, {"OrderDate", type text}, {"PartNum", Int64.Type}, {"Item Name", type text}, {"Count", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ClientID", "Name", "Surname", "OrderNum", "OrderDate", "PartNum", "Item Name", "Count", "Index"}, {"ClientID", "Name", "Surname", "OrderNum.1", "OrderDate", "PartNum", "Item Name", "Count", "Index"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Partition", "Merged", each Text.Combine({Text.From([PartNum], "en-US"), Text.From([Count], "en-US"), [Item Name]}, " "), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "1-st item", each if [Index] = 1 then [Merged] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "2-nd item", each if [Index] = 2 then [Merged] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "3-rd item", each if [Index] = 3 then [Merged] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "4-th item", each if [Index] = 4 then [Merged] else null),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "5-th item", each if [Index] = 5 then [Merged] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"})
in
    #"Removed Duplicates"

Best Regards

Maggie

[vishal17081990]

Hi  v-juanli-msft,

i was going through your post as am facing similar kind of scenario,

However i wonder if you could explain 3 rd step i.e. partition, after that step whole table is transformed into merged columns with orderNum

Step Partition

Looking forward for your help.

Regards,

VIshal.

[gerasimovav]

Hi, vishal17081990, it’s the smartest step. It is ingenious way to get the needed indexes of item inside order. It groups table by ordernum, and creates the indexes. This indexes are the key to put an item into right column “1 st” (index =1), “2 nd” (index = 2) etc.

[vishal17081990]

@gerasimovavindeed it is:)

can you guide me on how to do that particular step in query editor?

[gerasimovav]

@vishal17081990 I think this step is only aviable in Advanced Editor. 

You can get the similar result in query editor by clicking OrderNum and choose Group By then choose Operation "All Rows".

The result is 

Table.Group(#"Changed Type", {"OrderNum"}, {{"Count", each _, type table}})

let's compare with

Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})

The results are similar but without indexes, which are highly important for us.

[gerasimovav]

@v-juanli-msft, greatest thanks!

It looks like a magic!

But there is mistake in “Filled Up” Stage which I can’t still avoid.

If we firstly sort by ClientID, we’ve got:

Before “Filled Up”:

After:

2-nd item of the 45 Order jumps to the 42 Order 😞 

We need to fill to only same Order, not to all empty rows ...

[gerasimovav]

I've avoided this mistake by create another table. The First Column is OrderNum, the second is 1-st item, the third is 2-nd item ... The OrderNum is linked with OrderNum of @v-juanli-msft result without last 2 incorrect steps 

    #"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"})

 . The second and other columns look like 1-st item = LASTNONBLANK('List1'[1-st item];0).

And the task has solved absolutely.

[imnotabot01001]

great solution! but how would this be done in the query editor?

[Anonymous]

@gerasimovav you can do this in power query. 

1) Click on edit query in power bi and then go to the table which you want to pivot

2) In the transform tab there is a pivot column click on that (https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks)