Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RynoBotes
Frequent Visitor

Merged Cells in Source

Hi Team,

 

I have an issue where my source spreadsheet has merged cells in order to group different topics together. However when I add the data to PBI this is lost, which is understandable.

 

(could get the image upload to work sorry, so I hope I get this right)

 

SourceExample:

 

|JANUARY | FEBRUARY |

|1 |  2  |  3 | 4  | 5  |  6   |

 

After Import:

 

|JANUARY | null | null | FEBRUARY | null | null |

| 1             |   2  |   3   |     4           |   5  |   6   |

 

How can I make PBI work for me in order to get my columns/rows unique so that I can create the reports I need

 

 

2 ACCEPTED SOLUTIONS

Try following the article in link. It addresses your issue using PowerQuery/PowerBI using M query expression language.

https://wessexbi.wordpress.com/2014/02/27/unpivot-nested-headings-with-power-query/

 

This is pretty typical operation needed to analyze free census data that's provided by governments. Which are often organized using multi level headers.

View solution in original post

Awesome, I'll give it a read!

Thanks for the quick reply...

View solution in original post

7 REPLIES 7
imatic
New Member

Fill Down is what I needed. Thank you!

faisaldiab
New Member

You can merge cells or rows or columns values easily using Dose for Excel Add-In:
https://www.zbrainsoft.com/excel-merge.html

faisaldiab
New Member

You can merge cells or rows or columns values easily using Dose for Excel Add-In:
https://www.zbrainsoft.com/excel-merge.html

v-yuezhe-msft
Employee
Employee

@RynoBotes,

Please perform the steps below to handle your data in Power BI Desktop.

1. In Query Editor, choose the first row, then click on “Use First Row as Headers”.
1.PNG

2. Select the first three columns, then click on “Merge Columns”, choose appropriate Separator to merge columns.
3.PNG
2.PNG

3. Select the remaining columns, then click on “Merge Columns” to merge them and you will get expected result.
4.PNG

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

So Yes....technically your sollution works. Below you can see my dellema.

(BTW, not sure why the image upload failed on the original post)

 

I have my source showing budget tracking on project level (column A) for every month (Column C-H), along with the region owner (Column B).

 

source.JPGpbi.JPG

 

How can I create a link on Columns C-E so that PBI will know that only those columns are linked to January so that it will make creating reports easier.

 

Sorry for the crappy explination in the original post. And thanks again for helping me.

 

 

Try following the article in link. It addresses your issue using PowerQuery/PowerBI using M query expression language.

https://wessexbi.wordpress.com/2014/02/27/unpivot-nested-headings-with-power-query/

 

This is pretty typical operation needed to analyze free census data that's provided by governments. Which are often organized using multi level headers.

Awesome, I'll give it a read!

Thanks for the quick reply...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.