Power Query : Transpose Column To Rows Then Fill Down Date in Other Column

[muhammad susanto]

hi all..

i need formula into power query, how to transpose data column into data rows with criteria:
1. each region and other columns repeat 12 times (always) then
2. data column 'sold jan 22 till dec 22', transpose to rows;
3. add the custom column and fill down with date format (dd/mm/yyyy) from month Jan till Dec 22
here that layout table

tranpose complex.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	original data set
2	Region	Target	Actual	sold 'Jan 22	sold Feb 22	sold 'Mar 22	sold 'Apr 22	sold 'Mei 22	sold 'Jun 22	sold 'Jul 22	sold 'Agust 22	sold 'Sept 22	sold 'Okt22	sold 'Nov 22	sold 'Des22
3	A	65	40	10	5	6	7	6	8	9	0	6	3	4	12
4	B	60	20	20	4	5	6	5	7	6	8	9	9	9	9
5	C	50	30	30	10	10	10	10	10	10	12	12	12	12	12
6	D	55	23	23	15	16	20	20	20	20	20	20	20	20	20
7	E	65	25	25	16	30	30	30	30	30	30	30	30	30	30
8	F	70	35	30	17	45	45	45	45	45	45	45	45	45	45
9	G	62	60	21	21	50	65	65	4	65	5	65	65	65	65
10
11	after using formula in PQ
12	Region	Target	Actual	sold out	custom col. (as data format)- expected result
13	A	65	40	10	01/01/2022
14	A	65	40	5	01/02/2022
15	A	65	40	6	01/03/2022
16	A	65	40	7	01/04/2022
17	A	65	40	6	01/05/2022
18	A	65	40	8	01/06/2022
19	A	65	40	9	01/07/2022
20	A	65	40	0	01/08/2022
21	A	65	40	6	01/09/2022
22	A	65	40	3	01/10/2022
23	A	65	40	4	01/11/2022
24	A	60	40	12	01/12/2022
25	B	60	20	4	01/01/2022
26	B	60	20	5	01/02/2022
27	B	60	20	6	01/03/2022
28	B	60	20	5	01/04/2022
29	B	60	20	7	01/05/2022
30	B	60	20	6	01/06/2022
31	B	60	20	8	01/07/2022
32	B	60	20	9	01/08/2022
33	B	60	20	9	01/09/2022
34	B	60	20	9	01/10/2022
35	B	60	20	9	01/11/2022
36	B	60	20	9	01/12/2022
37	C
38	C
39	etc,,
Sheet1

anyone help, greatly appreciated

susant

 

[Automatrix]

In Power Query, choose all the sold [month] columns at once, then go to the Transform tab and select unPivot Columns. Here's the result for region A.

The do another table which associates "sold 'Jan 22" with 01/01/2022, and merge the two queries.

 

[muhammad susanto]

hi Automatrix, thank you so much...
please, how to add column date? in my sample in col E13 down..
sorry'i'm new bie about PQ.

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region", "Target", "Actual"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","sold"," ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","'"," ",Replacer.ReplaceText,{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Attribute", Text.Trim, type text}})
in
    #"Trimmed Text"

If you wish to continue to play in PQ, then I suggest you pick up this book by Ken Puls and Miguel Escobar Master Your Data with Power Query in Excel and Power BI

 

[muhammad susanto]

hi guys..thank you very much!!! working well

 

[alansidman]

Glad to have been of help. Thanks for feedback