Data shaping

[mariakos]

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

[mariakos]

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

[Anonymous · Accepted solution]

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.