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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

How to work with other column values after splitting a column into rows using delimiter?

I have a column called category in which few rows has many values separated by semi colon. I have splitted that column into rows using split by delimiter (:). Now the values in the other columns are repeating in few places because of which my average calculation is changing. How should I deal with this situation?

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

You can click on Remove Duplicates in the top bar

vangzhengmsft_1-1637720421619.png

If you want to split columns into rows, you can split the text and transform it into a table before expanding the columns.

I have created a sample to illustrate.

 

vangzhengmsft_0-1637720367864.png

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRysnK2clGK1YlWcgWTbmDSHUx6WHlaeVl5g9lOYNJLKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList(Text.Split([Category],":"))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Custom", {"Custom.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Category"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Column1", "Category"}})
in
    #"Renamed Columns"

 

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PhilipTreacy
Super User
Super User

Hi @Anonymous 

 

That's not how Power Query works.  If you split values as you have, you're going to get rows with values in the other columns repeated.

 

Maybe if you share some actual data and describe what it is you are trying to calculate, I can come up with a suitable solution.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

Hi @PhilipTreacy 

 

But I wanted them as rows only not columns and I do not want rows of few columns to get duplicated because of this. 

PhilipTreacy
Super User
Super User

Hi @Anonymous 

 

When you Split the CoIumn by Delimiter, Split Into Columns instead of Rows. 

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors