none
Split Column IF condition in another column is true RRS feed

  • Question

  • i am trying to write some M code to split a text string in 1 column if another column contains certain text... i keep getting errors

    i have a "each if [Type] = "BILLPAY" conditional in 4th line, not sure if its properly placed within the SplitColumns function

    let
        Source = Csv.Document(File.Contents("C:\Users\MTS\Documents\Activity Dec 2018.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Split Column by Position" = Table.SplitColumn(#"Promoted Headers", "Description",  each if [Type] = "BILLPAY" then Splitter.SplitTextByPositions({0, 29}, false) else null, {"Description.1", "Description.2"} )
        
        
        
        
    in
         #"Split Column by Position"



    mtsj

    Friday, December 28, 2018 7:33 PM

Answers

  • Hi mtsj

    From Colin:
    What your expression is saying is that for each value in [Type] that is "BILLPAY", then split the Description column, overwise don't split the column - so the Description column is split over and over anytime "BILLPAY" is encountered, but is unsplit and returns null (whatever that represents) if the value is not "BILLPAY". So depending on the last value of the 'Type' column, the column will be split or not. The entire logic makes no sense.

    Suggestion:

     let
        ...
        #"Added Custom" = Table.AddColumn(PreviousStepName, "Custom", each
            if [Type] = "BILLPAY" then
                Text.Combine({Text.Start([Description],29), Text.Middle([Description],29)},"|")
            else null
        ),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Type", "Custom"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Custom",
            Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Description.1", "Description.2"})
    in
        #"Split Column by Delimiter"

    • Marked as answer by mtsj Saturday, December 29, 2018 1:22 PM
    Saturday, December 29, 2018 7:34 AM

All replies

  • Hi mtsj,

    What your expression is saying is that for each value in [Type] that is "BILLPAY", then split the Description column, overwise don't split the column - so the Description column is split over and over anytime "BILLPAY" is encountered, but is unsplit and returns null (whatever that represents) if the value is not "BILLPAY". So depending on the last value of the 'Type' column, the column will be split or not. The entire logic makes no sense.

    The Table.SplitColumn function takes a splitter function as the third argument, and not an anonymous function that calls a splitter function. Thus it must be written as documented.

    Table.SplitColumn(#"Promoted Headers", Description, Splitter.SplitTextBy…, …)

    Friday, December 28, 2018 10:18 PM
  • thanks,if i re-write like below if still doesnt split any "BILLPAY" columns either...

    so what is the proper way to write this conditional ?

    #"Split Column by Position" = each if [Type] = "BILPAY" then Table.SplitColumn(#"Promoted Headers", "Description", Splitter.SplitTextByPositions({0, 29}, false), {"Description.1", "Description.2"}) else null
        


    mtsj

    Saturday, December 29, 2018 12:48 AM
  • Hi mtsj

    From Colin:
    What your expression is saying is that for each value in [Type] that is "BILLPAY", then split the Description column, overwise don't split the column - so the Description column is split over and over anytime "BILLPAY" is encountered, but is unsplit and returns null (whatever that represents) if the value is not "BILLPAY". So depending on the last value of the 'Type' column, the column will be split or not. The entire logic makes no sense.

    Suggestion:

     let
        ...
        #"Added Custom" = Table.AddColumn(PreviousStepName, "Custom", each
            if [Type] = "BILLPAY" then
                Text.Combine({Text.Start([Description],29), Text.Middle([Description],29)},"|")
            else null
        ),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Type", "Custom"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Custom",
            Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Description.1", "Description.2"})
    in
        #"Split Column by Delimiter"

    • Marked as answer by mtsj Saturday, December 29, 2018 1:22 PM
    Saturday, December 29, 2018 7:34 AM