Advanced Transformation Using Power Query

Power Query
1

Hi There,

I have stuck with one advance transformation using power query. I am attaching one excel file with problem and expected result which I would like to solve using power query in excel.

Please help me in this regards. Early response would be highly appreciated.

Thanks & Regards
Harish Rathore
Dashboard.xlsx (23.8 KB)

2

Hi there
You have the wrong forum to ask this question. You will have to ask the question on the excel forum.

thanks
Keith

3

Hi @Keith , thanks for your reply. This question is regarding power query hence i have posted over here. Can someone please help me in this regards?

Thanks
Harish Rathore

4

yes power query for power bi not excel(which you stated you wanted it done in excel)…wrong forum
thanks
Keith

5

I see there is a bit of confusion here, it is possible to use Power Query inside of Excel - in fact, that is where Power Query started. :slight_smile:

Attached is the Excel Power Query solution - and yes, the steps taken here would also work in PowerBi Desktop.

Basic steps required for this solution are:

  1. Transpose the table (so we can get the values on the first header line added to every column)
  2. Fill Down (see step above)
  3. Merge the Header columns together
  4. Transpose the table back to the original version
  5. Promote Headers
  6. Unpivot the value columns
  7. Fix Header names
  8. Split the merged Value Type and Brand Headers
  9. Pivot the Brand column back using the values as cell contents

One warning, you had Brand 5 listed twice in the source data, and that will NOT work, you’ll need to call that something else (I used Brand 6)

image
image1075Ă—142 11.7 KB

Dashboard (1) - with solution.xlsx (243.0 KB)

5 Likes
6

Thanks a ton @Heather . This is what I was expecting to get. I thought I would have to delete the post as mentioned by @Keith that this post doesnt belong here but then I thought that I had posted it under “Query Editor” then somebody should be able to help me. This format would be for my sales team to follow hence using Excel and wanted to use Power Query for automation. Huge thanks once again.

Regards
Harish Rathore

2 Likes
7

ok fine…i’ll keep my comments to myself

8

happy to help @harishrathore , if this works as a solution for your team, please mark my post as solution :slight_smile:

9

@Keith , @harishrathore and @Heather ,

I think this question has exposed an area of ambiguity that we need to clarify. While personally I now use Excel as the equivalent of a pack mule to carry my data into Power BI, there are very smart people with a completely different opinion on this – Ken Puls prominent among them who actually advocate for doing all data prep AND data modeling in Excel, and only then moving to Power BI for DAX and Visualization.

I believe that this forum can appropriately accommodate both views, and that questions regarding Power Query, whether done in Power BI or Excel are fair game here. However, that’s just my personal view and I think we need to clarify that more broadly with the Expert and Enterprise DNA Teams, and make the outcomes of those discussions clear to the forum members.

– Brian

5 Likes
10

All,

I ran this by the Expert team, and there was strong sentiment that Power Query is Power Query, regardless of whether it’s in Excel or Power BI Desktop, and that it’s appropriate to address within the forum. For some users whose offices don’t support Power BI Desktop, Excel is the only way they gain access to these tools.

Thanks to all for raising the issue – it’s clearly one we needed to provide more specific direction on.

– Brian

4 Likes
11

I would agree with this assessment, I don’t doubt there will be situations where it’s a Power Query for Excel problem but we/I could learn from the solution for use with Power BI.

2 Likes