Merged Cells in Source

[RynoBotes]

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

[Chihiro · Accepted solution]

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.

[imatic]

Fill Down is what I needed. Thank you!

[faisaldiab]

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

[faisaldiab]

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

[Anonymous]

@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”.


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



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

Regards,

[RynoBotes]

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).

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.

[Chihiro · Accepted solution]

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.

[RynoBotes · Accepted solution]

Awesome, I'll give it a read!

Thanks for the quick reply...