Column merging based on datatype

[batmanadk]

Hello guys! 
I'm stucked in one report and would really appreciate the help since I'm running out of time to turn this in.

So, this is for a restaurant that registers its clients in its POS system. 

Name: 
NIT:

Because of some operational issues, they had to swipe this, and now, the registration is 
NIT: 
Name: 

The problem is, the program doesn't register this info in columns, but it uses an entity to register all in one column as it follows:

Ticket Entity

{Customer name: A, Customer Nit: 111}

The problem is, that with the changed order, at some row point, data is different, like it follows

Date	Ticket Entity
01-01-2019	{Customer name: A, Customer Nit: 111}
01-01-2020	{Customer name: 112, Customer Nit: 112}

So, it is entering the NIT info in the customer name field... What I get after some transformations is this:

Customer Name	Customer Nit
A	111
112	B

 whereas it should be

Customer Name	Customer Nit
A	111
B	112

I've tried to do something in Power Query, such as:

Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([yourMixedCol], type number) then [yourMixedCol] else null), "txt", each if Value.Is([yourMixedCol], type text) then [yourMixedCol] else null)

I've also tried duplicating both columns and after that, applying 2 custom columns, like it follows:

= let parsed = try Number.From([Origional Text]) in [Number = parsed[Value]?, Text = if parsed[HasError] then [Origional Text] else null]

where [Original text] is [Customer Name] in the first duplicate, and [Customer Nit] in the second one. After that, I have to delete the duplicated columns and merge the original columns with the custom columns. It works, but I'm not sure if by taking so many steps in the query editor it'll affect performance... Do you think I could apply a better solution? 

. What do you guys think? Really appreciate the help

[pranit828]

Hi @batmanadk 

It sucks to see the requirement change but we are developers and we do what we have to do to get the required output and adhere to the timeline.

In this case I would create two new columns s below and use it all the charts and graphs.

Customer_name = IF(ISNUMBER([Customer Name]),[Ticket Entity],[Customer Name])  //Change the data type to text

Ticket_Entity    =  IF(ISNUMBER([Ticket Entity]),[Ticket Entity],[Customer Name])  //change the datatype to whole number

[batmanadk]

Thanks @pranit828  , yeah sorry about the edit, the thing is I got to a solution in the power query but it doesn't feel simple enough