Equivalent of Text to Columns in Power Query

[borkybork]

Hi! I created a column in power query with a mix of numbers, letters and symbols (ie. “1234567”, “A123456” or “A-1234567”). The current data type in PQ is “Int.64”. The problem is my Xlookup in excel is returning zero values. The formula works when I change the type to Number (in PQ) but the ones with letters and symbols give me an error (in PQ), and then hides them in excel. My workaround is to select the column then go to Data>Text to Columns in order for my xlookup to work. But I’d have to do that every time I refresh the table. Is there a way to do it in power query and avoid the extra step? Thanks in advance!

 

[alansidman]

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.

 

[borkybork]

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.

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]

I put both tables into PQ and did the math there

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

 

[borkybork]

I put both tables into PQ and did the math there

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

this is perfect, thank you so much!