How do you split multiple columns at the same time, based on an index column?

[Anonymous]

Hello,

I hope you are having a nice day. I need help with a power query function I've been trying to develop. I can't include specifics about the data, but I have included example tables below. I will do my best to explain what I need help with below.

Background:

I have an index column ([Index]) that is based on a group I split into rows. I created a max index column ([IndexMax]) that returned the max value of [Index] in each split group. I have many other delimited columns ([Column1], [Column2], ..., [ColumnN]) that need to be split based on the values in [Index].

Question:

I want to extract the value before/between/after the delimiter based on [Index]. I do not want to create new columns since there are so many columns I'd need to create this for. The tricky part is that some records have discrepancies where some rows in a column don't have as many values as there are index values (e.g., the third record in [Column1] has 3 values and its [IndexMax] value is 4).

Issue:

I tried creating a custom function with an if statement (example below), but each time I run this function through, I get an error stating a function can't be converted to text.

(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
    Extract

Example Source:

Index	IndexMax	Column1	ColumnN
1	2	A, A	H, I
2	2	A, A	H, I
1	3	B, C, D	J, J
2	3	B, C, D	J, J
3	3	B, C, D	J, J
1	4	E, F, G	K, L, N, N
2	4	E, F, G	K, L, N, N
3	4	E, F, G	K, L, N, N
4	4	E, F, G	K, L, N, N

Example Desired Result:

Index	IndexMax	Column1	ColumnN
1	2	A	H
2	2	A	I
1	3	B	J, J - Discrepancy
2	3	C	J, J - Discrepancy
3	3	D	J, J - Discrepancy
1	4	E, F, G - Discrepancy	K
2	4	E, F, G - Discrepancy	L
3	4	E, F, G - Discrepancy	N
4	4	E, F, G - Discrepancy	N

[AnalyticsWizard]

@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
    Extract

In 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.

[Anonymous]

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.

[lbendlin · Accepted solution]

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]

Hello,

Thank you for helping me with my issue. Your solution worked perfectly and I was able to achieve my desired result. I hope you enjoy the rest of your day.

Thank You,

Ben