Hi, I am using Power BI Desktop (2.110.1161.0 64-bit (October 2022)) and have a data connection to a SQL Server database containing personnel data, using an import query. I used Power Query to transform the data into a presentable table, which is all fine and dandy. However, one column uses a sort of loose XML format to store data that originated from a user form, and my problem is that I cannot find a way to slice or create a measure or filter with which I can use to further refine the report. Annoyingly, large numbers of the XML elements in the column are empty. So, I am looking for a way to filter on specific elements that contain things like firstname, lastname etc. Below is a sample (not real personal data), showing the format of the data in the column, which is set as a Text datatype.
<ept-6378>No</ept-6378>
<ept-6377>27/09/2010</ept-6377>
<ept-7001>firstname.lastname@nodomain.com</ept-7001>
<ept-6334>Maternity</ept-6334>
<ept-6339/>
<ept-6336/>
<ept-6337/>
<ept-8806>Yes</ept-8806>
<ept-6341/>
<ept-6340/>
<ept-9002>firstname.lastname@nodomain.com</ept-9002>
<ept-6323/>
<ept-8601/>
<ept-6244/>
<ept-6245>Title</ept-6245>
<ept-6804>Payroll Note 1</ept-6804>
<ept-6803>Yes</ept-6803>
<ept-6247>STAFF</ept-6247>
<ept-6343/>
<ept-6344/>
<ept-6345/>
<ept-6346/>
<ept-6347/>
<ept-6702>Check1</ept-6702>
<ept-6348/>
<ept-6349/>
<ept-7501>Approved</ept-7501>
<ept-6351>28/09/2010</ept-6351>
<ept-6350/>
<ept-6353/>
<ept-8103>LastnameF</ept-8103>
<ept-6101>504938</ept-6101>
<ept-6102>Firstname</ept-6102>
<ept-6103>Lastname</ept-6103>
<ept-6104>9203</ept-6104>
<ept-9410/>
<ept-8205/>
<ept-6358>Com</ept-6358>
<ept-6356/>
<ept-6357/>
<ept-6364/>
<ept-7902>30/10/2006</ept-7902>
<ept-9409>Maternity</ept-9409>
<ept-6363/>
<ept-8401>LONDON SE</ept-8401>
<ept-8402>COMMERCE TEAM</ept-8402>
<ept-6362/>
<ept-7802/>
<ept-6361/>
<ept-6360/>
<ept-7901/>
<ept-6224>NGV30940</ept-6224>
<ept-6225>Secretary</ept-6225>
<ept-6226>London Office 2A</ept-6226>
<ept-6227>London</ept-6227>
<ept-6369/>
<ept-6365/>
<ept-6366/>
<ept-6367/>
<ept-6368/>
<ept-6373/>
<ept-6375/>
<ept-6374/>
<ept-8201>1,2,3,4</ept-8201>
<ept-6371/>
<ept-9415/>
I'm relatively new to Power BI, but I want to develop my skills with this as I can see it is a very powerful tool, that will make presentation of this data, far clearer and easier to understand.
Thanks,
Pete
