Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm looking to write a Power Query function or Excel macro which would transform something like the first example below into something like the second - does anyone please have any suggestions?
Thanks so much.
-Brian
Solved! Go to Solution.
You may use ‘Split Column’ for the 3 columns in Query Editor.
Regards,
Cherie
You may use ‘Split Column’ for the 3 columns in Query Editor.
Regards,
Cherie
This is not directly made in PBI you can use this post to give you the expected result.
Using you data and this post I was abble to get the expected result see the M Code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slMTs0rzi8yVNJRCijKz0pNLjHUUYCyjICCIUBuCIgRFAxkBQUbKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Licensor = _t, Projects = _t, Territories = _t, RightSummary = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Licensor", type text}, {"Projects", type text}, {"Territories", type text}, {"RightSummary", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Project", each Text.Split([Projects],", ")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Territorie", each Text.Split([Territories],", ")), #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Summary", each Text.Split([RightSummary], ", ")), #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.FromColumns({[Project],[Territorie],[Summary]})), #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom2", "Custom.3", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Projects", "Territories", "RightSummary", "Project", "Territorie", "Summary"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Projects"}, {"Column2", "Territories"}, {"Column3", "RightSummary"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Licensor", type text}, {"Projects", type text}, {"Territories", type text}, {"RightSummary", type text}}) in #"Changed Type1"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |