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

[Anonymous]

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?

[v-angzheng-msft]

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]

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.

[PhilipTreacy]

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

[Anonymous]

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]

Hi @Anonymous 

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

Regards

Phil