locked
Extract data/numbers from a column containing mixed data types RRS feed

  • Question

  • Hi,

    I have a column which contains mixed data types - although in DAX it is set to text. An example would be:

    Test

    57

    01/12/2014

    78

    Test 2

    Is there of way of calculating all numerical values (numbers and dates) and extract them to a calculated column where the data type can be set to number?

    Thanks,

    James

    Monday, December 1, 2014 12:52 PM

Answers

  • The context was that this was how the data was stored in the MySQL database table and I wanted to extract it without going through unnecessary steps.

    I have however instead used Power Query to filter the MySQL table to only values that contain the date/time and number values that I require and imported that table in my PowerPivot model - it is just more hassle because I now need to update the PowerQuery excel each time before I can refersh my PowerPivot moe.

    • Marked as answer by Maracles Tuesday, December 2, 2014 10:30 AM
    Tuesday, December 2, 2014 10:30 AM

All replies

  • Why are you ending up with data column like this in PowerPivot ?

    Data Quality should be handled during ETL process either in SQL database or using ETL operations in PowerQuery if you are using it.

    You can write a complex DAX and extract different calculated columns but not a best practice/recommended solution.

    Cheers,

    MM 

    • Proposed as answer by MM-99 Tuesday, December 2, 2014 8:56 PM
    Monday, December 1, 2014 10:35 PM
  • The context was that this was how the data was stored in the MySQL database table and I wanted to extract it without going through unnecessary steps.

    I have however instead used Power Query to filter the MySQL table to only values that contain the date/time and number values that I require and imported that table in my PowerPivot model - it is just more hassle because I now need to update the PowerQuery excel each time before I can refersh my PowerPivot moe.

    • Marked as answer by Maracles Tuesday, December 2, 2014 10:30 AM
    Tuesday, December 2, 2014 10:30 AM