Pivot or Unpivot?

Hello Anonymous 

You can achieve this in Power Query by using Group By and Text.Combine. Here's how:

Steps in Power Query

1. Load your data into Power Query.
2. Select the "Code" and "Grp" columns, then go to Transform → Group By.
3. In the Group By window:
• Group by: Code and Grp
• New Column Name: Merged_Name
• Operation: All Rows
4. Click OK → this will create a grouped table.
5. Click on the small icon in the grouped column to expand the "Name" and "Qtr_Year" columns.
6. Create a custom column using this formula:

powerquery

CopyEdit

Text.Combine([Qtr_Year], " ") & " " & Text.Combine([Name], " ")

This merges the values from both columns into a single row.

1. Remove unnecessary columns if needed.
2. Click Close & Load to return the transformed data to Excel or Power BI

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

pankajnamekar25 Thanks it works till Step 6 but

powerquery

CopyEdit

Text.Combine([Qtr_Year], " ") & " " & Text.Combine([Name], " ")

This merges the values from both columns into a single row.

1. Remove unnecessary columns if needed.
2. Click Close & Load to return the transformed data to Excel or Power BI

I get error or maybe I am missing something. Thanks

Hi Anonymous 
Sorry for the late response.
Please do folow the mentioned points in detail to resolve your issue.

1. Load your data into Power Query.
2. Add an Index Column (Add Column → Index → From 1).
3. Add a custom column called Is_Date with this formula:
   = if Text.Contains([Qtr_Year], "Q") then "Yes" else "No"
4. Filter rows where Is_Date = Yes → keep this as Table_Qtr.
5. Filter rows where Is_Date = No → keep this as Table_Ctgy.
6. In both tables, keep the Index column.
7. In Table_Qtr, rename Qtr_Year to Qtr_Year.
8. In Table_Ctgy, rename Qtr_Year to Ctgy.
9. Merge Table_Qtr with Table_Ctgy on Index using Inner Join.
10. Expand the merged column and select only Ctgy.
11. Remove Index and Is_Date columns.
12. Final output will have columns: Code, Grp, Name, Qtr_Year, Ctgy.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code ", "Grp ", " Name"}, {{"Count", each Text.Combine(_[Qtr_Year],"")}})
in
    #"Grouped Rows"

Hope this helps.

Hi Anonymous 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by Ashish_Excel , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Best Regadrs
Cheri Srikanth

Hi Anonymous 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.