Colum mismatch

[SDVN]

Hi,

Am new to power BI, i am trying to learn on my own. 

I have a situation where there is a mismatch in the column as shown in the below picture. 

I want Name, AGE, SEX, CITY, EDUCATION, NATIONALITY, SKILLS as header. I tried to do it but as NATIONALITY and SKILLS are in the 3rd row, am not able to get them to header in power BI.

I tried doing it by dividing the table into 2 but it didn't work. Is there any easier way?

Thank you 🙂

[TheoC]

@SDVN yes there definitely is a way.  Please follow these simple steps:

1. Go into Power Query by clicking on Transform Data. Your Power Query table will look like this:

2. Duplicate the query so you have two version.

3. In Table1, remove Column 6 and Column 7, then click on "Use First Row as Headers" under the Home ribbon.  Remove blanks or nulls, and add an "Index Column" starting from 1. Your output should look like this:

4. In Table2 (the duplicate query), remove Column 1 to Column 5.  Then, filter out all blanks so there are no more, then "Use First Row as Headers". Once you have done that, add an Index Column starting from 1. The output will look like the below:

5. Go to Merge Queries (or Merge Queries as New) and apply the following:

6. In the new Merge1 query, go to the far right column which will be your Table2 column and click the expand button. Untick "Index" and "Use original column name as prefix" and click ok.

7. Your new table is as below:

Hope this helps 🙂

Theo

[SDVN]

Hi @TheoC, 

Thank you very much! I get it quite frequently but as am still learning  Power BI, I correct it in EXCEL. I am curious to learn if there is a way to do it in power BI as I was told that by the tuitor that its much "sophesticated" than excel. :D. I would be helpful even if you can direct me to some youtube video in case its too elaborate to describe here. 

[TheoC]

@SDVN yes there definitely is a way.  Please follow these simple steps:

1. Go into Power Query by clicking on Transform Data. Your Power Query table will look like this:

2. Duplicate the query so you have two version.

3. In Table1, remove Column 6 and Column 7, then click on "Use First Row as Headers" under the Home ribbon.  Remove blanks or nulls, and add an "Index Column" starting from 1. Your output should look like this:

4. In Table2 (the duplicate query), remove Column 1 to Column 5.  Then, filter out all blanks so there are no more, then "Use First Row as Headers". Once you have done that, add an Index Column starting from 1. The output will look like the below:

5. Go to Merge Queries (or Merge Queries as New) and apply the following:

6. In the new Merge1 query, go to the far right column which will be your Table2 column and click the expand button. Untick "Index" and "Use original column name as prefix" and click ok.

7. Your new table is as below:

Hope this helps 🙂

Theo

[TheoC]

Hi @SDVN 

Is the mismatch something that will be frequent in your data sets or is this a once off issue?

As ridiculous as it sounds, if it is a once-off problem, it may be easier just to go to your data source, select the two cells, right-click, delete cells, and select "shift-up" to get everything realigned. It's a 10 sec job versus a few minutes in Power Query, that's all.

I'll wait to hear from you and then provide a solution in Power Query if you need Power BI to resolve this 🙂

[v-zhangti]

Hi, @SDVN 

As for the asymmetry of the last two rows, you may need to modify them in the data source. You can select the above two spaces to delete the cells and move the other rows up.

You can use Use first row as headers in the Power query editor.

The results are shown in the figure：

If the method I provided above can't solve your problem, what's your expected result?

Can you provide more detailed information about the source data?

Best Regards

Community Support Team _Charlottez  Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SDVN]

I use this method but is there a way to fix it in power BI? 

[TheoC]

@v-zhangti thank you for using my response to demonstrate your response. 

[TheoC]

Hi @SDVN 

Is the mismatch something that will be frequent in your data sets or is this a once off issue?

As ridiculous as it sounds, if it is a once-off problem, it may be easier just to go to your data source, select the two cells, right-click, delete cells, and select "shift-up" to get everything realigned. It's a 10 sec job versus a few minutes in Power Query, that's all.

I'll wait to hear from you and then provide a solution in Power Query if you need Power BI to resolve this 🙂