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.
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.
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 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |