Forum Discussion
Data shaping
Hi,
I need some help shaping the data in the table below:
I have 2 issues as described below:
Problem 1: Reordering Columns
Label Definitions: Columns labeled 1, 2, and 3 correspond to year, country, and topics, respectively. I need assistance rearranging these columns to reflect this logical order. Alternatively, this could be addressed by creating a reference table, such as I've seen with date tables, to map the labels accordingly.
Standardising: Ensure that all columns have consistent headers (e.g., "year," "country," "topics").
Problem 2: Separating Multiple Values
Rows with multiple values (e.g., FR, NO, DE under the label 'country') need to be organised so that each value is listed separately under 'country,' allowing for the selection of individual values like FR for visualisation purposes
Mapping to column headers: Ensure values are assigned to their appropriate logical column headers.
Looking forward to hearing from you and your possible solutions to my issues. Thank you 🙂
| Impressions | Reach avg. | Frequency | Engagements | Engagement rate | CTR | |||||||||
| 6120 | 3750 | 1,63 | 96 | 1,57 | 11,67 | 1. 2024 | 3. Employer Branding | 2. DE | ||||||
| 2473 | 1745 | 1,42 | 60 | 2,43 | 2,67 | |||||||||
| 2958 | 2238 | 1,32 | 24 | 0,81 | 27,35 | 1. 2024 | 2. DE | 3. Financial Reporting | ||||||
| 1045 | 669 | 1,56 | 22 | 2,11 | 2,68 | 1. 2024 | 2. FR | |||||||
| 2040 | 1347 | 1,51 | 44 | 2,16 | 3,48 | 1. 2024 | 3. Press release | 2. IT | ||||||
| 3244 | 2205 | 1,47 | 61 | 1,88 | 2,5 | 1. 2024 | 2. ES | |||||||
| 3749 | 2744 | 1,37 | 87 | 2,32 | 2,16 | 1. 2024 | 3. Financial Reporting | 2. NO | ||||||
| 34656 | 26521 | 1,31 | 355 | 1,02 | 19,02 |
Hi mariakos,
Translate the data in power query, rename column 1,2,3 to year, country and topics first:
Then split the country column with multiple values with delimiter:
In my test, I used a comma as the delimiter, and you can choose a different delimiter if needed.
Before:
Later:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi mariakos,
Translate the data in power query, rename column 1,2,3 to year, country and topics first:
Then split the country column with multiple values with delimiter:
In my test, I used a comma as the delimiter, and you can choose a different delimiter if needed.
Before:
Later:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your help. Sorry for the delay in my response. I was away and had limited access to the internet.
The data doesn't feed into Power BI clearly like you have interpreted it with column 1, column 2 and column 3. Each data input is labelled with either 1, 2 or 3 (not the columns -which are more than 3). These are also not ordered from 1-3 as you can see in the image but scattered throughout more than 3 columns across the data input. Is there a way to structure this?
Thank you Maria
