Equivalent of Text to Columns in Power Query

I'm having a bit of an issue visualizing what you want to do. Please load some sample data using XL2BB and then show a mocked up solution for the items shown. 6-8 records is probably all we need to see in order to give you a viable PQ solution.

alansidman said:

I'm having a bit of an issue visualizing what you want to do. Please load some sample data using XL2BB and then show a mocked up solution for the items shown. 6-8 records is probably all we need to see in order to give you a viable PQ solution.

Click to expand...

Thanks for responding and sorry for the late reply! I can't get the XL2BB to work atm, but I hope the screenshots can help visualize my data.
The goal is to get the amount paid from table 2 by doing an xlookup of the Invoice number from Table 1.



Table 1 is a PQ table and the problem is my xlookup is returning 0 values for Joe and Ringo. The data type in PQ is Int.64, but when you change it to a number, Joe's and Ringo's invoice #s are showing Error and are not in the output table. The best solution I found was to keep the Int.64 data type in PQ and then do Text To Columns on the Invoice Number, but then you have to do that each time you refresh the table. I have several worksheets so I'd like to be able to just refresh them without the extra step. Hope this helps clarify things!

alansidman said:

I put both tables into PQ and did the math there

Power Query:

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CT = Table.TransformColumnTypes(T1,{{"Invoice Number", type text}, {"Invoice Amount", Currency.Type}}),
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    CT2 = Table.TransformColumnTypes(T2,{{"Invoice Number", type text}, {"Amount Paid", Currency.Type}}),
    MQ = Table.NestedJoin(CT, {"Invoice Number"},CT2, {"Invoice Number"}, "Tbl", JoinKind.LeftOuter),
    Expand= Table.ExpandTableColumn(MQ, "Tbl", {"Amount Paid"}, {"Amount Paid"}),
    Difference= Table.AddColumn(Expand, "Subtraction", each [Invoice Amount] - [Amount Paid], Currency.Type),
    RC = Table.RenameColumns(Difference,{{"Subtraction", "Balance Remaining"}})
in
    RC

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Invoice Number	Invoice Amount		Name	Invoice Number	Amount Paid		Name	Invoice Number	Invoice Amount	Amount Paid	Balance Remaining
2	Jane Smith	1234567	100000		Jane Smith	1234567	20000		Jane Smith	1234567	100000	20000	80000
3	Joe Schmoe	A-1234567	200000		Joe Schmoe	A-1234567	30000		Joe Schmoe	A-1234567	200000	30000	170000
4	Ringo Starr	AC12345678	300000		Ringo Starr	AC12345678	40000		Ringo Starr	AC12345678	300000	40000	260000
Sheet1

Click to expand...

this is perfect, thank you so much!