Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
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.
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
Proud to be a Super User!
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"
Proud to be a Super User!
User | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
143 | |
112 | |
73 | |
55 |