Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi - I'm new to Power BI and not sure if this is possible. I've searched through the forum but might be searching for the wrong terms.
I have the following Example data in columns where in the Name column there is the family name and also subsets. The Type distinguishes the Family name from the subsets (Type A family name Type B subset). I am trying to find a way to get 'Result Wanted'. I've tried transforming data, using text delimiters etc but as there are so many variables, haven't had any success. Any help appreciated.
Name | Type | Result Wanted |
ABC | A | ABC |
ABC-Test | B | ABC |
ABC - Run | B | ABC |
ABC - Tested | B | ABC |
DEF-G | A | DEF-G |
DEF-G - Testers | B | DEF-G |
DEF-G-break | B | DEF-G |
DEF-G - Tester | B | DEF-G |
H I J - K L | A | H I J - K L |
H I J - K L - Tester | B | H I J - K L |
H I J - K L - break | B | H I J - K L |
H I J - K L - Run | B | H I J - K L |
Solved! Go to Solution.
Here's the M code to do it. Paste into the Advanced Editor in Power Query and examine the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcgRhICtWByyiG5JaXAIUckIVVtBVCCrNwyoO0pCagibl4uqm6w41HcKGi8K0FBVD9aDJ6yYVpSZmY5eD68WQ9lDwVPACSnsr+ECtRRZBU4FuDH6lyO7BrxIRRCjqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, #"Result Wanted" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Result Achieved", each if [Type] = "A" then [Name] & " - ARTIFICIAL" else [Name]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Result Achieved", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Result Achieved.1", "Result Achieved.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Result Achieved.2"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Result Achieved.1", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Result Achieved.1", "Result Achieved"}})
in
#"Renamed Columns"
Best
D
Hi,
You should get the result you want with Split Column.
Choose delimiter - and tick, Most Right Delimiter.
It should work.
Otherwise try with Columns from Example, and type in the first values you want.
Have a nice day,
@AilleryO Thank you however I have about 100k of rows with 1000s of Type As and haven't been able to get the result desired.
Here's the M code to do it. Paste into the Advanced Editor in Power Query and examine the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcgRhICtWByyiG5JaXAIUckIVVtBVCCrNwyoO0pCagibl4uqm6w41HcKGi8K0FBVD9aDJ6yYVpSZmY5eD68WQ9lDwVPACSnsr+ECtRRZBU4FuDH6lyO7BrxIRRCjqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, #"Result Wanted" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Result Achieved", each if [Type] = "A" then [Name] & " - ARTIFICIAL" else [Name]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Result Achieved", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Result Achieved.1", "Result Achieved.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Result Achieved.2"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Result Achieved.1", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Result Achieved.1", "Result Achieved"}})
in
#"Renamed Columns"
Best
D
@Anonymous YEEKS! Many thanks - i'll have a go!
@Anonymous Thank you so much !
That works on the data i provided and will now try with the data I have. Huge thank you!
@Anonymous Many thanks I think I have it working. Can I confirm that null values in Result Achieved.2 would confirm that Type B isn't able to find a Type A? I assume there must be some typos in the data.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |