cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SDVN
New Member

Colum mismatch

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. 

SDVN_1-1634502131926.png

 

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 🙂

8 REPLIES 8
TheoC
Memorable Member
Memorable Member

@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:

TheoC_0-1634768546827.png

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:

 

TheoC_3-1634769860385.png

 

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:

TheoC_4-1634769925113.png

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

TheoC_5-1634770000401.png

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.

TheoC_6-1634770112360.png

 

7. Your new table is as below:

TheoC_7-1634770145503.png

Hope this helps 🙂

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

SDVN
New Member

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
Memorable Member
Memorable Member

@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:

TheoC_0-1634768546827.png

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:

 

TheoC_3-1634769860385.png

 

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:

TheoC_4-1634769925113.png

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

TheoC_5-1634770000401.png

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.

TheoC_6-1634770112360.png

 

7. Your new table is as below:

TheoC_7-1634770145503.png

Hope this helps 🙂

 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

v-zhangti
Community Support
Community Support

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.

vzhangti_0-1634710313877.png

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

vzhangti_1-1634710356315.jpeg

The results are shown in the figure:

vzhangti_2-1634710372886.png

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.

 

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

TheoC
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors