Convert a data type from text to two options

[ina]

Hello,

I want to test the prediction model in the AI builder by using my own data, which is an excel file. In order to do that I have created an entity by pressing:

• data --> entity --> get data 

and there I have chosen to load into a new entity. When I am about to chose the data type for the fields I only have two options: "text" and "multiline text". But what I want is to choose "two options" since I am going to use the prediction model.  I know that once a field is saved I can’t change the data type except from “text” to “autonumber”.

So I tried to delete the entity and get it again. But I do not see where I am supposed to set the data type as “two options” while I am in the process of getting the data (the excel file). I can't see any  option for selecting “two options”…

Do you have any idea how I can fix it?

Best regards,
Ina

[v-siky-msft]

@ina 

We can only choose "text" and "multiline text" type in power query, this is by design.

My workaround is to generate the two options field later in the PowerApps, so you first import excel to text field, and then creat a two option set field in entity.

Then in the PowerApps, convert the text field to Two options set field in bulk.

Take Account entity as an example, I will convert M_Text column to TwoOption column.

1. create a collection name col1, to save the Account ID and imported Text column

ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"))

2. use ForAll function to iterate each collection record, and patch value to TwoOption column.

ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))

Sik

[v-siky-msft]

@ina 

We can only choose "text" and "multiline text" type in power query, this is by design.

My workaround is to generate the two options field later in the PowerApps, so you first import excel to text field, and then creat a two option set field in entity.

Then in the PowerApps, convert the text field to Two options set field in bulk.

Take Account entity as an example, I will convert M_Text column to TwoOption column.

1. create a collection name col1, to save the Account ID and imported Text column

ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"))

2. use ForAll function to iterate each collection record, and patch value to TwoOption column.

ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))

Sik

[ina]

@v-siky-msft 

Thank you a lot for the answer!

If I understand you correctly, I need to import the excel data in power query and thereafter I'll make kind of "an extra" field that is two option field. Then I am little bit lost... M_Text is a field within the Account entity in your case and I am wondering where you are located when you convert the field from being text to being two option? (do you make new App for it, and if so where can you put these functions in) 

Best regards,
Ina

[v-siky-msft]

@ina 

Yes, I create a PowerApps to execute the above codes to convert the Text field(M_Text) to Two Option(TwoOption).

1. Create an app, connect to the entity where you import the excel file.

2. Add a button, paste the above two codes to OnSelect property of button as below

ClearCollect(Col1,RenameColumns(ShowColumns(Accounts,"crf0d_m_text","accountid"),"crf0d_m_text","MT1","accountid","AID"));
ForAll(Col1,If(MT1="Yes",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.Yes}),MT1="No",Patch(Accounts,LookUp(Accounts,Account=AID),{TwoOption:'TwoOption (Accounts)'.No})))

3. You need to change the entity name and column name to yours, e.g. YourEntity, TextFiedName, EntityGUID, TwoOptionFieldName.

ClearCollect(Col1,RenameColumns(ShowColumns('YourEntity',"TextFieldName","EntityGUID"),"TextFieldName","MT1","EntityGUID","AID"));
ForAll(Col1,If(MT1="Yes",Patch('YourEntity',LookUp('YourEntity','EntityGUID'=AID),{'TwoOptionFieldName':'TwoOptionFieldName (YourEntity)'.Yes}),MT1="No",Patch('YourEntity',LookUp('YourEntity','EntityGUID'=AID),{'TwoOptionFieldName':'TwoOptionFieldName (YourEntity)'.No})))

Please let me know if this doesn't work.

Sik

[ina]

Hi Sik,

Thank you for the effort you have put into helping me! I would like to send you a screenshot of how it is in my computer and the error that I am getting since I have no idea of how to fix it. I would appreciate it a lot if you could help me out. 

The entity name is: cr4d9_hotelcancellation

The text field name is: cr4d9_revenue

The entity GUID is: cr4d9_hotelcancellationid

The two option field name is: cr4d9_prufa

Best regards,

Ina

[v-siky-msft]

@ina 

Please use Entity name of entity and Display Name of field.

I guess your entity name is 'Hotel Cancellations' and the Text field display name is Revenue, Two option is Prufa, GIUD field is 'Hotel Cancellation'. 

Then your formula should be:

ClearCollect(Col1,RenameColumns(ShowColumns('Hotel Cancellations',"Revenue","Hotel Cancellation"),"Revenue","MT1","Hotel Cancellation","AID"));
ForAll(Col1,If(MT1="Yes",Patch('Hotel Cancellations',LookUp('Hotel Cancellations','Hotel Cancellation'=AID),{Prufa:'Prufa (Hotel Cancellations)'.Yes}),MT1="No",Patch('Hotel Cancellations',LookUp('Hotel Cancellations','Hotel Cancellation'=AID),{Prufa:'Prufa (Hotel Cancellations)'.No})))

Note: The two option Items is Yes and No.

Sik

[ina]

Hello @v-siky-msft,

So, I am still stuck with the prediction model. I made the app, connected the entity to it and I get errors saying e.g. "The specified column revenue does not exist" which is one of the field that is within the entity and the same goes for the other field "Hotel Cancellation". 

Do you have any idea what I am doing wrong or how I can fix it? 

Best regards,
Ina

[ina]

Hello @v-siky-msft,

So, I am still stuck with the prediction model. I made the app, connected the entity to it and I get errors saying e.g. "The specified column revenue does not exist" which is one of the field that is within the entity and the same goes for the other field "Hotel Cancellation". 

Do you have any idea what I am doing wrong or how I can fix it? 

Best regards,
Ina