Forum Discussion
modeling data
You could transform this into a proper table. I will post an example below...
Let's say, you are starting with the following imported data... (remove any step that promotes 1st row as headers)
Step 1: Identify the pivot columns. In this case, those are the first 3 columns. Select those 3 pivot columns and merge them together, use a delimiter like a semicolon (;) or any other character that won't be there in the data values of these columns.
It will give you the following...
Step 2: Transpose this table using the following option.
This will transpose the table and give you the following...
Step 3: Use "Fill Down" on those pivot columns that have null values in them.
This will give you the following output.
Step 4: Promote the first row as headers
Step 5: Select the first 3 Pivot columns and unpivot the other columns. (Use Shift+Click or Ctrl+Click to select multiple columns)
This will give you the following output.
Step 6: Split back the merged column using the same delimiter that was used earlier.
This will give you the following output.
You could further rename the column names, change the data types, and so on. Use these steps as a general idea. The actual steps that you need could be different depending on your input file. But it will be a combination of similar steps.
You don't have to know any advanced stuff to do all these. These are pretty simple steps like merging, transposing, splitting, unpivoting, renaming column headers, choosing the correct data types etc... If you observe your input file and think about how to sequence these simple transformations to get the final output, you will be able to do it.
You could transform this into a proper table. I will post an example below...
Let's say, you are starting with the following imported data... (remove any step that promotes 1st row as headers)
Step 1: Identify the pivot columns. In this case, those are the first 3 columns. Select those 3 pivot columns and merge them together, use a delimiter like a semicolon (;) or any other character that won't be there in the data values of these columns.
It will give you the following...
Step 2: Transpose this table using the following option.
This will transpose the table and give you the following...
Step 3: Use "Fill Down" on those pivot columns that have null values in them.
This will give you the following output.
Step 4: Promote the first row as headers
Step 5: Select the first 3 Pivot columns and unpivot the other columns. (Use Shift+Click or Ctrl+Click to select multiple columns)
This will give you the following output.
Step 6: Split back the merged column using the same delimiter that was used earlier.
This will give you the following output.
You could further rename the column names, change the data types, and so on. Use these steps as a general idea. The actual steps that you need could be different depending on your input file. But it will be a combination of similar steps.
You don't have to know any advanced stuff to do all these. These are pretty simple steps like merging, transposing, splitting, unpivoting, renaming column headers, choosing the correct data types etc... If you observe your input file and think about how to sequence these simple transformations to get the final output, you will be able to do it.
I know this post is quite old. But I am currently working through a similar process with a project.
What I would like to know is,
Would it better to keep your data at the data source formatted similar to the orginal post, and then transform the data in Power BI.
or
Model the data in the format you suggested at the data source.
1) If you can format the data at the source itself, that is better rather than doing all these transformations in PowerQuery. Forget the technical reasons, it will be easy for someone to else go through the code and debug or edit it later if there is a requirement. This will also help in identifying any error in the data immediately by determining if the error is the source or in the transformation.
2) Other than that, the major difference is that if you add any columns or perform some calculations in Power Query, it will be performed before importing the data and the transformed data will be stored in persistent storage. But if you add a calculated column in DAX, it is not stored and everytime it is calculated during the data refresh and these columns will occupy the RAM & CPU. Generally, I prefer to add any such columns either in the source or using Power Query and then import the data and avoid using Calculated Columns in DAX. Ofcourse, there are some exceptions.
Thank you for this, it is extremely helpful!
