Power query - How to convert Categories to a column

[grawri]

What Power query formula would i need to convert the Shop ID that appears in my Ref column into it's own column as per the second data sample?


Ref Rep Date Amount
101 Melbourne
JKL0345 PJ 1/07/2017 453.98
HLK0462 KL 1/07/2017 364.78
IDE876 PJ 1/07/2017 234.85
POL234 KL 1/07/2017 129.73
125 Collingwood
TYB003 VG 1/07/2017 876.34
RFI879 DC 1/07/2017 465.43
EDW553 DC 1/07/2017 198.23
PIJ556 WS 1/07/2017 223.45


Ref Rep Date Amount Shop ID
JKL0345 PJ 1/07/2017 453.98 101
HLK0462 KL 1/07/2017 364.78 101
IDE876 PJ 1/07/2017 234.85 101
POL234 KL 1/07/2017 129.73 101
TYB003 VG 1/07/2017 876.34 125
RFI879 DC 1/07/2017 465.43 125
EDW553 DC 1/07/2017 198.23 125
PIJ556 WS 1/07/2017 223.45 125

 

[Oeldere]

Book1
	A	B	C	D	E
28	Ref	Rep	Date	Amount
29	101	Melbourne			101
30	JKL0345	PJ	1-7-2017	453,98	101
31	HLK0462	KL	1-7-2017	364,78	101
32	IDE876	PJ	1-7-2017	234,85	101
33	POL234	KL	1-7-2017	129,73	101
34	125	Collingwood			125
35	TYB003	VG	1-7-2017	876,34	125
36	RFI879	DC	1-7-2017	465,43	125
37	EDW553	DC	1-7-2017	198,23	125
38	PIJ556	WS	1-7-2017	223,45	125
Blad2

Cell Formulas
Range	Formula
E29	=IF($D29="",$A29,$E28)

 

[MarcelBeug]

In Power Query:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Shop Id", each if [Date] = null then [Ref] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Shop Id"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Date] <> null and [Date] <> "")
in
    #"Filtered Rows"

 

[grawri]

Perfect Marcel. Thank you.