Power BI: How do I check if a record is a date value and split a column based on that value?

Question

I have no experience with Power BI or PowerQuery. I'm pulling records from an XML file to create a graph of how many hours worked on a given day. I need a date column and an hours column. In the XML file the tags are only listed as FIELD, not DATE or HOURS, so it all collects in one column. I was thinking that I create a custom column with a formula to differentiate the dates from the numeric values and then have two columns, one with hours and one with dates. Someone suggested that I also add an index column, but I don't know the syntax to use any of this. Is there a way to do this?

Here's the XML:

<RECORDS>
<COUNT>20</COUNT>
<DISPLAY_LIST_START>1</DISPLAY_LIST_START>
<DISPLAY_LIST_STOP>20</DISPLAY_LIST_STOP>
<STOP>20</STOP>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/01/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/02/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>3</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/03/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>2</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/06/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE><![CDATA[2.5]]></VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/05/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>2</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/08/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>6</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/09/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/10/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/11/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/12/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>4</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/15/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE><![CDATA[4.5]]></VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/16/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE><![CDATA[4.5]]></VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/21/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>6</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/22/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>2</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/24/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>3</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/25/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>3</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/26/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>4</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/27/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>3</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/29/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
<RECORD>
<SESSION_ID>OGYkhXOUbcnefM6RZkvRHUipY3QIiX64bPTKRmOz</SESSION_ID>
<FIELD>
<VALUE><![CDATA[06/30/2020]]></VALUE>
</FIELD>
<FIELD>
<VALUE>5</VALUE>
</FIELD>
</RECORD>
</RECORDS>

And here's what the column looks like:

Answer 1

Going from your screenshot, you can determine what type it is by checking if it contains / in it with a custom column named Type:

if Text.Contains([VALUE], "/") then "Date" else "Number"

From there, you can pivot on that new Type column: