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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
FJS
Regular Visitor

Grouping Column

Hi,

It might be a stupid question. I tried but not able to find the perfect result.

 

I am trying to convert multiple columns into group rows in power query.

 

Below are the examples.

 

Data which I have (its a sample I have lot of columns after new department) :

NameOld CityNew CityOld Department

New Department

JamesNew YorkColumbusFinanceProcurement
HarryMesaDallasProcurementAnalytics

 

Data (Result) which I want in power query:

NameChange typeOld ValueNew Value
JamesCityNew YorkColumbus
JamesDepartmentFinanceProcurement
HarryCityMesaDallas
HarryDepartmentProcurementAnalytics

 

Thanks in Advance for your help!!

1 ACCEPTED SOLUTION

Hi @FJS 

This error indicates that you are trying to convert a text type value into Number type. This usually happens in the Changed type step. Please check whether the value "FC0001" appears in "PCI" column. If so, change its column type to text instead. 

 

Change below step from

 

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PCI", Int64.Type}, {"PCI Name", type text}, {"PCI Name_1", type text}, {"SiteIn", type text}, {"SiteIn_2", type text}}),

 

to

 

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PCI", type text}, {"PCI Name", type text}, {"PCI Name_1", type text}, {"SiteIn", type text}, {"SiteIn_2", type text}}),

View solution in original post

6 REPLIES 6
FJS
Regular Visitor

Thanks for the reply.

 

 

 

If it is possible can you show me in other ways.

 

 

 

It would be best if you can show in the way which have no coding in it.

 

As my client might also need to do this in future by himself.

Hi @FJS 

 

You can paste the code provided by @m_dekorte into a blank query's Advanced Editor, click OK. Then you will see the result table. In the Applied Steps pane on the right side, there are detailed steps of this query. Click every step from top to bottom to check how it works step by step. 

vjingzhanmsft_0-1710403516362.png

 

For a deep dive on how to incorporate M code from the forums into your code, you can refer to the following resources:

Utilizing M Code Samples Given as Solutions in Power Query

Power BI Forum Help: How to integrate M-code into your existing solution

 

Don't be afraid of the M code. You will find that copying and pasting the M code is easier than replicating all steps one by one manually. And M code can be saved as a backup. 

 

Feel free to let us know if you're not sure how a step is implemented.

 

Best Regards,
Jing

Hi @v-jingzhan-msft @m_dekorte ,

 

I tried pasting code in my file but it is giving me error. IT looks like i have the number in the name column.

 

Below is the code and snippet of the error.

Error snippet

FJS_0-1710415166473.png

 

 

Code:

let

Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Column4", "Column5", "Column6", "Column7", "Column10", "Column11", "Column12", "Column13", "Column14"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "Column2", "Column8", "Column3", "Column9"}),
#"Removed Top Rows" = Table.Skip(#"Reordered Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PCI", Int64.Type}, {"PCI Name", type text}, {"PCI Name_1", type text}, {"SiteIn", type text}, {"SiteIn_2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"PCI Name", "Old PCM Name"}, {"PCI Name_1", "New PCM Name"}, {"SiteIn", "Old SiteM"}, {"SiteIn_2", "New SiteM"}}),

UnpivotOthers = Table.UnpivotOtherColumns(#"Renamed Columns", {"PCI"}, "Attribute", "Value"),
SplitByDelimiter = Table.SplitColumn(UnpivotOthers, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
MergeColumns = Table.CombineColumns(SplitByDelimiter,{"PC", "Attribute.2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
PivotColumn = Table.Pivot(MergeColumns, List.Distinct(MergeColumns[Attribute.1]), "Attribute.1", "Value"),
SplitColumn = Table.SplitColumn(PivotColumn, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"City", "Change Type"})
in
SplitColumn

 

Hi @FJS 

This error indicates that you are trying to convert a text type value into Number type. This usually happens in the Changed type step. Please check whether the value "FC0001" appears in "PCI" column. If so, change its column type to text instead. 

 

Change below step from

 

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PCI", Int64.Type}, {"PCI Name", type text}, {"PCI Name_1", type text}, {"SiteIn", type text}, {"SiteIn_2", type text}}),

 

to

 

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PCI", type text}, {"PCI Name", type text}, {"PCI Name_1", type text}, {"SiteIn", type text}, {"SiteIn_2", type text}}),

FJS
Regular Visitor

FJS_0-1710356632956.png

Attaching snippet for more clarity.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors