Power Query : Transpose Value Into 4 Department

[muhammad susanto]

hi all..
the following table layout , that i want to transpose into 4 department with 0/blank value based on criteria in col a (department)

split column.xlsx
	A	B	C	D	E	F	G	H
1	original data			after using power query/power bi
2	Department	All Budget			col1	col2	col3	col4
3	Asset	10		Asset	10	0	0	0
4	Asset	2		Asset	2	0	0	0
5	Asset	5		Asset	5	0	0	0
6	Asset	0		Asset	0	0	0	0
7	Asset	11		Asset	11	0	0	0
8	Asset	12		Asset	12	0	0	0
9	Maintenance	2		Maintenance	0	2	0	0
10	Maintenance	3		Maintenance	0	3	0	0
11	Maintenance	4		Maintenance	0	4	0	0
12	Maintenance	11		Maintenance	0	11	0	0
13	Maintenance	12		Maintenance	0	12	0	0
14	Maintenance	23		Maintenance	0	23	0	0
15	Maintenance	12		Maintenance	0	12	0	0
16	Maintenance	10		Maintenance	0	10	0	0
17	Sales	55		Sales	0	0	55	0
18	Sales	5		Sales	0	0	5	0
19	Sales	12		Sales	0	0	12	0
20	Sales	11		Sales	0	0	11	0
21	Sales	10		Sales	0	0	10	0
22	Marketting	3		Marketting	0	0	0	3
23	Marketting	4		Marketting	0	0	0	4
24	Marketting	12		Marketting	0	0	0	12
25	Marketting	3		Marketting	0	0	0	3
26	Marketting	45		Marketting	0	0	0	45
27	Marketting	11		Marketting	0	0	0	11
28	Marketting	2		Marketting	0	0	0	2
29	Marketting	45		Marketting	0	0	0	45
Sheet2

my desired result in start from col1, col2,col3, col4 down
how to solve this problem
i hope one of you, give me figure out.

susant

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ld = List.Distinct(Source[Department]),
    lst = List.Zip({ld, {1..List.Count(ld)}}),
    tbl = List.Accumulate(lst, Source, (s,c)=> Table.AddColumn(s, "col" & Text.From(c{1}), each if [Department] = c{0} then [All Budget] else 0)),
    Result = Table.RemoveColumns(tbl, {"All Budget"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	Department	All Budget		Department	col1	col2	col3	col4
2	Asset	10		Asset	10	0	0	0
3	Asset	2		Asset	2	0	0	0
4	Asset	5		Asset	5	0	0	0
5	Asset	0		Asset	0	0	0	0
6	Asset	11		Asset	11	0	0	0
7	Asset	12		Asset	12	0	0	0
8	Maintenance	2		Maintenance	0	2	0	0
9	Maintenance	3		Maintenance	0	3	0	0
10	Maintenance	4		Maintenance	0	4	0	0
11	Maintenance	11		Maintenance	0	11	0	0
12	Maintenance	12		Maintenance	0	12	0	0
13	Maintenance	23		Maintenance	0	23	0	0
14	Maintenance	12		Maintenance	0	12	0	0
15	Maintenance	10		Maintenance	0	10	0	0
16	Sales	55		Sales	0	0	55	0
17	Sales	5		Sales	0	0	5	0
18	Sales	12		Sales	0	0	12	0
19	Sales	11		Sales	0	0	11	0
20	Sales	10		Sales	0	0	10	0
21	Marketting	3		Marketting	0	0	0	3
22	Marketting	4		Marketting	0	0	0	4
23	Marketting	12		Marketting	0	0	0	12
24	Marketting	3		Marketting	0	0	0	3
25	Marketting	45		Marketting	0	0	0	45
26	Marketting	11		Marketting	0	0	0	11
27	Marketting	2		Marketting	0	0	0	2
28	Marketting	45		Marketting	0	0	0	45
29
Sheet1

 

[muhammad susanto]

hi JGordon11, thank you very much!! Working well..

 

[muhammad susanto]

hi JGordon11...
why error for this show message
Expression.Error: The column 'Department' of the table wasn't found.
Details:
Department

let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSfR-Hr0YY18Bj-3kCEd78pp5DNEbwChno4hQNbELutjLxly0W6bMFq2kR6qfBYT-I8b3d8Fd0V2RT_/pub?output=xlsx"), null, true),
    pnbp_all_Sheet = Source{[Item="pnbp_all",Kind="Sheet"]}[Data],
    ld = List.Distinct(Source[Department]),
    lst = List.Zip({ld, {1..List.Count(ld)}}),
    tbl = List.Accumulate(lst, Source, (s,c)=> Table.AddColumn(s, "col" & Text.From(c{1}), each if [Department] = c{0} then [All Budget] else 0)),
    Result = Table.RemoveColumns(tbl, {"All Budget"})
in
    Result

how fix this problem?

 

[JGordon11]

SInce I can't see your data, I'm not sure - maybe reference pnbp_all_Sheet instead of Source in the ld and tbl steps

 

[muhammad susanto]

hi...
this link sample capkin 2022 by sst
in sheet "pnbp_all"
i'm often happen to me... i don't why this this problem..
i hope you would help me again