Pivot or Unpivot?

[Anonymous]

Hi All,

I have in power query the following table

Code  Grp   Name Qtr_Year

400	23	BGT	Q1_2025
400	23	BGT	Apple

Now I want the below output

Code  Grp   Name Qtr_Year Ctgy

400

23

BGT

Q1_2025

Apple

I have 10000 rows of Codes in the data

Can you please help?

Thanks

[v-csrikanth]

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.

[v-csrikanth]

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

[Ashish_Excel]

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.

[v-csrikanth]

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.

[Anonymous]

@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

[pankajnamekar25]

@Anonymous 

try this

[Qtr_Year] & " " & [Name]

Thanks,
Pankaj

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

[Anonymous]

@pankajnamekar25 Unfortunately something is wrong with the logic.

Code  Grp   Name Qtr_Year

400	23	BGT	Q1_2025
400	23	BGT	Apple

 500    24     AVT  Q3_2024

 500    24     AVT  Orange

Please refer to the above format and advise. Thanks

[pankajnamekar25]

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.