Group orders in the same cell, regarding the scheduled hour and supplyer name.

[HUHA]

Hello.
I have a table the following data :

	A	B	C	D
1​	Supplyer name	Order number	Scheduled date	Scheduled hour
2​	Supplyer 1	00421492	20191209​	1100​
3​	Supplyer 1	00421493	20191209​	1100​
4​	Supplyer 1	00421604	20191209​	1100​
5​	Supplyer 2	00421605	20191209​	1100​
6​	Supplyer 3	00416136	20191209​	1200​
7​	Supplyer 3	00416155	20191209​	1300​
8​	Supplyer 3	00416161	20191209​	1400​
9​	Supplyer 5	00421938	20191209​	900​
10​	Supplyer 4	00421970	20191209​	600​
11​	Supplyer 4	00421971	20191209​	600​
12​	Supplyer 4	00421972	20191209​	600​
13​	Supplyer 4	00422126	20191209​	600​
14​	Supplyer 4	00422127	20191209​	600​

I need some help to bring the data in this format
When i have the same scheduled hour for the same supplyer , the order numbers to come in the same cell, with "/" betwen them, on one row
If i have different scheduled hours for the same supplyer, then to have the orders on the same cell only if the hour is the same.

Supplyer name	Orders	Scheduled hour
Supplyer 1	00421492/00421493/00421604	1100
Supplyer 2	00421605	1100
Supplyer 3	00416136	1200
Supplyer 3	00416155	1300
Supplyer 3	00416161	1400
Supplyer 4	00421970/00421971/00421972/00422126/00422127	600
Supplyer 5	00421938	900

what the formula should be ?

Thank you in Advance

 

[sandy666]

with Power Query

Supplyer name	Order number	Scheduled date	Scheduled hour		Supplyer name	Orders	Scheduled hour
Supplyer 1	421492	20191209	1100		Supplyer 1	421492/421493/421604	1100
Supplyer 1	421493	20191209	1100		Supplyer 2	421605	1100
Supplyer 1	421604	20191209	1100		Supplyer 3	416136/416155/416161	1200
Supplyer 2	421605	20191209	1100		Supplyer 3	416136/416155/416161	1300
Supplyer 3	416136	20191209	1200		Supplyer 3	416136/416155/416161	1400
Supplyer 3	416155	20191209	1300		Supplyer 4	421970/421971/421972/422126/422127	600
Supplyer 3	416161	20191209	1400		Supplyer 5	421938	900
Supplyer 5	421938	20191209	900
Supplyer 4	421970	20191209	600
Supplyer 4	421971	20191209	600
Supplyer 4	421972	20191209	600
Supplyer 4	422126	20191209	600
Supplyer 4	422127	20191209	600

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp = Table.Group(Source, {"Supplyer name"}, {{"Count", each _, type table}}),
    Orders = Table.AddColumn(Grp, "Orders", each List.Distinct(Table.Column([Count],"Order number"))),
    Extract = Table.TransformColumns(Orders, {"Orders", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
    Hours = Table.AddColumn(Extract, "Scheduled hour", each List.Distinct(Table.Column([Count],"Scheduled hour"))),
    Expand = Table.ExpandListColumn(Hours, "Scheduled hour"),
    Sort = Table.Sort(Expand,{{"Supplyer name", Order.Ascending}}),
    Type = Table.TransformColumnTypes(Sort,{{"Scheduled hour", Int64.Type}, {"Orders", type text}})
in
    Type

 

[HUHA]

Thank you sandy66 for your input.
Now i try to replicate in Power Query how to do it. Is there a windows, where can i write your text , in the window of power query editor ?

 

[sandy666]

if you want create it from the beginning select your "table" and from the ribbon - data - find there From Table
it will open PowerQuery Editor, then you can replicate steps from the post

 

[HUHA]

thank you very much.
I did it, i put your code in Advance editor from power query editor

 

[sandy666]

it was second option

 

[HUHA]

It does not work like intended.
If you look at the results of Supplyer 3 , it should be only one order at every Scheduled hour .
416136 for scheduled hour 1200
416155 for scheduled hour 1300
416161 for scheduled hour 1400

the code does not look at the scheduled hour.

Do you know how to improve the code to take into consideration the scheduled hour, not only the Supplyer name ?

 

[HUHA]

i think i did it

Here

Grp = Table.Group(Source, {"Supplyer name"}, {{"Count", each _, type table}}),


i add

Grp = Table.Group(Source, {"Supplyer name" , "Scheduled hour "}, {{"Count", each _, type table}}),

 

[sandy666]

very well, congrats
so whole code looks like this:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp = Table.Group(Source, {"Supplyer name", "Scheduled hour"}, {{"Count", each _, type table}}),
    Orders = Table.AddColumn(Grp, "Orders", each List.Distinct(Table.Column([Count],"Order number"))),
    Extract = Table.TransformColumns(Orders, {"Orders", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
    Sort = Table.Sort(Extract,{{"Supplyer name", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Supplyer name", "Count", "Orders", "Scheduled hour"}),
    Type = Table.TransformColumnTypes(Reorder,{{"Scheduled hour", Int64.Type}, {"Orders", type text}})
in
    Type

error humanum est

 

[sandy666]

shame on me, should be: errare humanum est