Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Before:
Name | Species | Activity | Start Date | End Date |
Pusheen | Feline | Snacking | 10/1/2019 | 10/10/2019 |
Pusheen | Feline | Napping | 10/2/2019 | 10/12/2019 |
Puppy | Doggie | Playing | 10/3/2019 | 10/13/2019 |
After:
Name | Species | Activity 1 | Start Date 1 | End Date 1 | Activity 2 | Start Date 2 | End Date 2 |
Pusheen | Feline | Snacking | 10/1/2019 | 10/10/2019 | Napping | 10/2/2019 | 10/12/2019 |
Puppy | Doggie | Playing | 10/3/2019 | 10/13/2019 | NA | NA | NA |
How do I do this in Power BI Desktop? Thanks!
Solved! Go to Solution.
Hi daisyhu,
You could try to use below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouSE3OTC0GshyTSzLLMksqQYIliUUlCi6JJSAVrnkpEGasTrRSQGlxRmpqHlDYLTUnMw9sQl5icnZmXjqQaWigb6hvZGBoCWUbQDg4NPolFhTA9Rkh6zNC1ldQAHKSS356eiZIV0BOYiVclzGyLignNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Activity", "Start Date", "End Date"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 0, 1),
#"Grouped Rows1" = Table.Group(#"Added Index", {"Name", "Species"}, {{"ALL", each _, type table [Name=text, Species=text, Merged=text, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([ALL], "GID", 1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Merged", "GID"}, {"Custom.Merged", "Custom.GID"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US")[Custom.GID]), "Custom.GID", "Custom.Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2", "1.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"2.1", "2.2", "2.3"})
in
#"Split Column by Delimiter1"
Then rename column name to you want.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi daisyhu,
You could try to use below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouSE3OTC0GshyTSzLLMksqQYIliUUlCi6JJSAVrnkpEGasTrRSQGlxRmpqHlDYLTUnMw9sQl5icnZmXjqQaWigb6hvZGBoCWUbQDg4NPolFhTA9Rkh6zNC1ldQAHKSS356eiZIV0BOYiVclzGyLignNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Activity", "Start Date", "End Date"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 0, 1),
#"Grouped Rows1" = Table.Group(#"Added Index", {"Name", "Species"}, {{"ALL", each _, type table [Name=text, Species=text, Merged=text, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([ALL], "GID", 1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Merged", "GID"}, {"Custom.Merged", "Custom.GID"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US")[Custom.GID]), "Custom.GID", "Custom.Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2", "1.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"2.1", "2.2", "2.3"})
in
#"Split Column by Delimiter1"
Then rename column name to you want.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |