Forum Discussion
NiugeS
Helper V
Helper VSplit Columns with matching value in multiple rows
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 N...
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
NiugeSHelper V
Helper VAilleryO 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
- NiugeS
Anonymous YEEKS! Many thanks - i'll have a go!
- NiugeS
Anonymous Thank you so much !
That works on the data i provided and will now try with the data I have. Huge thank you!- NiugeS
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.