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

 

[HUHA]

was a lucky guess, that i have to put , "Scheduled hour " over there.

I still dont get the logic of this line
Extract = Table.TransformColumns(Orders, {"Orders", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
but in time i will understand it.

Any how , thank you again , i could not do it without you.

 

[sandy666]

maybe play with Sort

// 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}),
    Reorder = Table.ReorderColumns(Extract,{"Supplyer name", "Count", "Orders", "Scheduled hour"}),
    Sort = Table.Sort(Reorder,{{"Supplyer name", Order.Ascending}, {"Scheduled hour", Order.Ascending}}),
    Type = Table.TransformColumnTypes(Sort,{{"Scheduled hour", Int64.Type}, {"Orders", type text}})
in
    Type

Extract will extract grouped orders not to records ( not to each rows) but to the single record with / between each order (horizontal)
Click on the "cell" with Table name but not on stricte Table name and look down and you will see what is under the each Table

 

[sandy666]

 

[sandy666]

two lines: Orders & Extract

 

[HUHA]

Thank you Sandy666 for your info.
From today i will try to work with power query more often. I see it has some good options.

 

[sandy666]

You are welcome
Have a nice day