Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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?
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.
Hi, @Anonymous
You can click on Remove Duplicates in the top bar
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.
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.
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
Proud to be a Super User!
But I wanted them as rows only not columns and I do not want rows of few columns to get duplicated because of this.
Hi @Anonymous
When you Split the CoIumn by Delimiter, Split Into Columns instead of Rows.
Regards
Phil
Proud to be a Super User!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.