Search code examples
powerbietldaxpowerquerym

Having trouble transforming this dataset for ETL

I'm playing around with some datasets on Kaggle.com, trying to learn better practices for ETL, as I tend to get stuck with specific things with the transform part. For this question, I am dealing with the survey results from Stack Overflow 2018: https://www.kaggle.com/stackoverflow/stack-overflow-2018-developer-survey - specifically the LanguageWorkedWith column. enter image description here

Currently I am using a combination of RapidMiner/Excel to attempt to change the data. I am not well versed in R and Python code enough to solve this problem with coding methods.

The problem with the current column, is it lists all the languages that a user has chosen separated by a semi-colon. I can easily split a column on a semi-colon, but what occurs is either 2 things:

  1. I have 31 columns of LanguageWorkedWith1 - LanguageWorkedWith31. This makes gathering a count of languages by salary to not work.

  2. A cartesian effect where each row would be duplicated to accommodate only the choice of language. So you'll have a lot of duplicate rows, which definitely affects the integrity of the data. I have also tried using Power BI ( the Load location) to remove duplicates on the responder ID and language, but that didnt work.

Ideally I'd like to do a language by salary visual in Power BI, similar to how many kernals have it, but cant figure out the process for making this happen outside of code. Not sure how this would look exactly, but if i can split all the languages and count them, I can at least do something like this: enter image description here But I'm not sure if i can relate this back to salary with how the data is.

I just want to understand some transforming processes better! Appreciate any help!

Solution

  • The key here is to split into rows instead of columns.

    Split to rows

    So that you end up with a table like this:

    Expanded rows

    You can keep that row expansion in its own related table in your data model so you aren't creating a giant table.

    Data Model

    From there it's pretty easy to make visuals provided you know a little bit of DAX. For example, I created an AvgSalary measure (after converting that column to a numeric type) like this:

    AvgSalary =
CALCULATE (
    AVERAGE ( survey_results_public[ConvertedSalary] ),
    FILTER (
        survey_results_public,
        survey_results_public[Respondent] IN VALUES ( 'Language'[Respondent] )
    )
)

    and was then able to create interesting charts like the following:

    AvgSalary