Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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:

IndexIndexMaxColumn1ColumnN
12A, AH, I
22A, AH, I
13B, C, DJ, J
23B, C, DJ, J
33B, C, DJ, J
14E, F, GK, L, N, N
24E, F, GK, L, N, N
34E, F, GK, L, N, N
44E, F, GK, L, N, N

 

Example Desired Result:

IndexIndexMaxColumn1ColumnN
12AH
22AI
13BJ, J - Discrepancy
23CJ, J - Discrepancy
33DJ, J - Discrepancy
14E, F, G - DiscrepancyK
24E, F, G - DiscrepancyL
34E, F, G - DiscrepancyN
44E, F, G - DiscrepancyN

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

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.

View solution in original post

4 REPLIES 4
AnalyticsWizard
Solution Supplier
Solution Supplier

@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
Not applicable

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
Super User
Super User

 

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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.