Power Query Columns

[bbrancotronex]

Hi,
I have the following data

Table1
INV_NUM INV_DTE
12345 01/02/23
23456 01/10/23
23455 01/13/23

Table 2
INV_NUM TYPE AMOUNT
12345 1 10.00
12345 3 20.00
23456 1 15.00
23456 4 20.00

Results
INV_NUM INV_DTE VALUETYPE1 VALUETYPE3 VALUETYPE4
12345 01/02/23 10.00
12345 01/02/23 20.00
23456 01/10/23 15.00
23456 01/10/23 20.00
23455 01/13/23

Can this be done in Power Query? Basically I n

 

[JEC]

Merge queries on the INV_NUM with a left outer join. Then expand the table column

 

[bbrancotronex]

Merge queries on the INV_NUM with a left outer join. Then expand the table column

That will show me the fields in table 2, but I dont see how it would put it in columns based on the type column?

 

[JEC]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source_two = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{{"type", type text}}),
    merge = Table.NestedJoin(Source, {"num"}, Source_two, {"num"}, "Table2", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(merge, "Table2", {"type", "amount"}, {"type", "amount"}),
    dup = Table.DuplicateColumn(exp, "num", "numm"),
    piv = Table.Pivot(dup, List.Sort(List.Distinct(dup[#"type"])), "type", "numm", List.Count),
    out = Table.TransformColumnNames( piv, (x)=>  if List.Contains({1..9}, try Number.FromText(x) otherwise x) then "VALUETYPE" & x else x)
in
    out

Book1
	A	B	C	D	E	F	G	H	I	J
1	num	test		num	type	amount
2	1	a		1	9	10
3	2	b		1	5	20
4	3	c		2	6	15
5				2	1	20
6				2	4	25
7				3	5	5
8				3	7	10
9
10
11
12
13	num	test	amount	VALUETYPE1	VALUETYPE4	VALUETYPE5	VALUETYPE6	VALUETYPE7	VALUETYPE9
14	1	a	10	0	0	0	0	0	1
15	1	a	20	0	0	1	0	0	0
16	2	b	15	0	0	0	1	0	0
17	2	b	20	1	0	0	0	0	0
18	2	b	25	0	1	0	0	0	0
19	3	c	5	0	0	1	0	0	0
20	3	c	10	0	0	0	0	1	0
21
Sheet1

 

[bbrancotronex]

This is a better example of what I need for the input and output
This is a better example of the input and output I need

INPUT
Inv #	INV Dte	Category Code	Category	Amt
1234	2/1/2023​	1​	Freight	$ 30.00
1234	2/1/2023​	2​	Processing Fee	$ 25.00
1234	2/1/2023​	3​	Credit Card	$ 15.00
2345	2/3/2023​	1​	Freight	$ 6.00
2345	2/3/2023​	2​	Processing Fee	$ 12.00
2345	2/3/2023​	4​	Handling	$ 16.00
Output
Inv #	INV Dte	Category 1	Category 2	Category 3	Category 4
1234	2/1/2023​	$ 30.00	$ 25.00	$ 15.00
2345	2/3/2023​	$ 6.00	$ 12.00		$ 16.00

​	​

 

[JEC]

In that case it's easier. You should pivot on Amount. Based on my previous example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source_two = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{{"type", type text}}),
    merge = Table.NestedJoin(Source, {"num"}, Source_two, {"num"}, "Table2", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(merge, "Table2", {"type", "amount"}, {"type", "amount"}),
    piv = Table.Pivot(exp, List.Sort(List.Distinct(exp[#"type"])), "type", "amount", List.Sum),
    out = Table.TransformColumnNames( piv, (x)=>  if List.Contains({1..9}, try Number.FromText(x) otherwise x) then "VALUETYPE" & x else x)
in
    out

 

[bbrancotronex]

Thanks, I am new to Power Query, so where would this script go?

 

[JEC]

create two tables named as table1 and table2
Then open a blank query via get data. Go to the advanced editor and paste the code