Forum Discussion
How do you split multiple columns at the same time, based on an index column?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYzLDYAwDENXsXL2BcoC5U9BLFB1/zVwOfREixQrkf3iGK0zWi95wmvtxGGJ8fW+/Mw7aSQmYtYViFBeKpGrR7lwkBZiJTZdJ3ERt6bUNgH3BwxNID0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, IndexMax = _t, Column1 = _t, ColumnN = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"IndexMax", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index", "IndexMax"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Value],each if List.Count(Text.Split([Value],", "))=[IndexMax] then Text.Split([Value],", "){[Index]-1} else [Value] & " - Discrepancy",Replacer.ReplaceValue,{"Value"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"IndexMax", Order.Ascending}, {"Index", Order.Ascending}}) in #"Sorted Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Anonymous
The issue you’re encountering might be due to the way you’re referencing the column values in your function. In Power Query, column values are typically referenced using a record, not directly as function parameters1.
Here’s a modified version of your function that might work:
(Index as number, IndexMax as number, Column as text)=>
let
Extract =
if Column = null then null
else if List.Count(Text.PositionOf(Column, "delimiter", Occurrence.All)) + 1 < IndexMax then Column & " - Discrepancy"
else if Index = 1 and Index = IndexMax then Column
else if Index = 1 and Index <> IndexMax then Text.Replace(Column, Text.BeforeDelimiter(Column, "delimiter", 0))
else if Index <> IndexMax then Text.Replace(Column, Text.BetweenDelimiters(Column, "delimiter", "delimiter", Index - 2))
else if Index = IndexMax then Text.Replace(Column, Text.AfterDelimiter(Column, "delimiter", Index - 2))
else null
in
ExtractIn this version, I’ve removed the square brackets around the Column references. This is because in your function definition, Column is a parameter that already refers to a text value, so it doesn’t need to be referenced as a column in a record1.
Remember, these are just potential solutions. The exact solution might vary depending on the specifics of your data and the cause of the issue1.
Hello,
Thank you for trying, but now I get the following error: "Expression.Error: We cannot apply field access to the type Text."
I get this error when I try to "Table.TransformColumns" to all the columns I need split. My code looks like this:#"Extracted Text" = Table.TransformColumns(#"Previous Step",{ {"Column1", each Extract([#"Index - Split"], [#"Index Max - Split"], [#"Column1"]), type text}, {"Column2", each Extract([#"Index - Split"], [#"Index Max - Split"], [#"Column2"]), type text}, {"ColumnN", each Extract([#"Index - Split"], [#"Index Max - Split"], [#"ColumnN"]), type text}})When I add a new column with the function, though, it works perfectly. I just can't seem to transform all the columns I need in mass.