Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
After playing around with both Power Query and Dax functions, I can officially say that I'm stuck.
I have a column in my table that looks like this:
Project Number | Complexity Counts |
1 | Simple (2), Medium (1) |
2 | Medium (5) |
3 | Large (1), Simple (1) |
4 | Mini (.5) |
5 | Mini (1), Medium (3) |
I want to both split by delimeter and pivot the columns so that my table looks like this:
Project Number | Simple | Medium | Large | Mini |
1 | 2 | 1 | 0 | 0 |
2 | 0 | 5 | 0 | 0 |
3 | 1 | 0 | 1 | 0 |
4 | 0 | 0 | 0 | .5 |
5 | 0 | 3 | 0 | 1 |
Here is what I have tried:
1. Power Query -
Project | Complexity Counts 1.1 | Complexity Counts 1.2 |
1 | Simple (2) | Medium (1) |
2 | Medium (5) | null |
3 | Large (1) | Simple (1) |
4 | Mini (.5) | null |
5 | Mini (1) | Medium (3) |
Project | Complexity Counts 1.1 | Complexity Counts 1.1.1 | Complexity Counts 1.2 | Complexity Counts 1.2.1 |
1 | Simple | 2 | Medium | 1 |
2 | Medium | 5 | null | null |
3 | Large | 1 | Simple | 1 |
4 | Mini | .5 | null | null |
5 | Mini | 1 | Medium | 3 |
Project | Simple | Medium | Large | Mini | Complexity Counts 1.2 | Complexity Counts 1.2.1 |
1 | 2 | 1 | null | null | Medium | 1 |
2 | null | 5 | null | null | null | null |
3 | 1 | null | 1 | null | Simple | 1 |
4 | null | null | null | .5 | null | null |
5 | null | 3 | null | 1 | Medium | 3 |
Issues with this:
Dax:
Solved! Go to Solution.
Hi @kboscar1
Please paste the following code into the advanced editor and follow the steps. I've broken them up into digestible steps (hopefully):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzC3ISVXQMNLUUfBNTckszVXQMNRUitWJVjICSsOETCFCxkAhn8Si9FSQIh0FmGaoBhOQhsy8TAUNPah6U7iIIZL5xkDJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, #"Complexity Counts" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"Complexity Counts", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Complexity Counts], "," )),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Custom", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Custom.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Complexity Counts"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Custom.2")
in
#"Pivoted Column"
... also enclosing the file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @kboscar1
Please paste the following code into the advanced editor and follow the steps. I've broken them up into digestible steps (hopefully):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzC3ISVXQMNLUUfBNTckszVXQMNRUitWJVjICSsOETCFCxkAhn8Si9FSQIh0FmGaoBhOQhsy8TAUNPah6U7iIIZL5xkDJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, #"Complexity Counts" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"Complexity Counts", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Complexity Counts], "," )),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Custom", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Custom.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Complexity Counts"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Custom.2")
in
#"Pivoted Column"
... also enclosing the file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hey @kboscar1
Your final screenshot looks correct except that it has the two extra columns. Can't you just delete those columns once you are at the point or create a reference query and the use the formulas outlined in this thread to replicate the index/match feature with a LOOKUPVALUE: https://community.powerbi.com/t5/Desktop/power-bi-quot-Index-Match-quot/td-p/119419
?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |