Split of a Field Values

[Sudharshan1919]

Hi All,

Below format is my data.

Date         MachineName            AssociatedUsers

1-Jan              VM1                         Usr1, Usr2, Usr5

2-Jan              VM2                         Usr4

2-Jan              VM3                         Usr3,Usr6

I want the data like as below format.

Date         MachineName            AssociatedUsers

1-Jan              VM1                         Usr1

1-Jan              VM1                         Usr2

1-Jan              VM1                         Usr5

2-Jan              VM2                         Usr4

2-Jan              VM2                         Usr3

2-Jan              VM2                         Usr6

Kindly help me to achieve this result.

Thanks

[v-zhangti]

Hi, @Sudharshan1919 

You can try the following methods.
First:

Result:

Second:

Make two more copies of the table, leaving one column of AssociatedUsers for each table. Replace the column names of the three tables with consistency.

Third:

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pratyashasamal]

Hi @Sudharshan1919 ,
In PowerQuery Editor , go to  choose Rows >Split Column >Split Column using delimiter.

Thanks ,
Pratyasha Samal .
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[negi007]

@Sudharshan1919 

here is the code and also the pbix file.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrMU9JRCvM1BJKhxUWGOgpA0ghMmirF6gDVGMHVGEHUmKCLG0PEjXWAhJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, MachineName = _t, AssociatedUsers = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"MachineName", type text}, {"AssociatedUsers", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "AssociatedUsers", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"AssociatedUsers.1", "AssociatedUsers.2", "AssociatedUsers.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"AssociatedUsers.1", type text}, {"AssociatedUsers.2", type text}, {"AssociatedUsers.3", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"AssociatedUsers.1", "AssociatedUsers.2", "AssociatedUsers.3"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
#"Removed Columns"