Power Query to Remove 2nd and 3rd Comma

[nschmidt]

I have a report that kicks out a comma separated text file. The second column in the file has company name and some of them have commas in their name. I have successfully identified the count of commas using the following equation:

= Table.AddColumn(#"Changed Type", "Custom", each List.Count(Text.Split([Column1],","))-1)

Now that I know the number of commas any row that has 14 and 15 have company names that have commas in them. What function in power query could I use to remove the 2nd and or 3rd comma in those identified rows? Once I do that I can text to column the report and it will be clean. Thanks!

[AntrikshSharma · Accepted solution]

@nschmidt Paste this code in Advanced editor:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "lZHLbsMgEEV/BWU9bZgxfu6IQQmqDZbtOEnTyKt+QdX/r7HdKE9VZYHgzoXDHY7HRaetcnVvFMwrK0sNlfPKMCvZjrvCWN3nbmtbaLRt+87IXisDSRj2bS1tI/PWODtbSmN7XxlPx2HwxOIroyUJo6e3RLMF+TPLUPGWxQmOCwySNBaw0SZ3L4XL36ApK4lhSgKQL4MlcSJAOHx+gR/oVT7JjKcZJybLScVJna/FmBMos3eWdbLoNPv45pwiNryjGpqzhqKxBwxSvMNcIzATnFXlLRp5xvGMFlfkNODQbGu90/WebRvJjM0htxiQGJ71N0884uFl1PBfSTfrsuGUTE26J+NMpoweksmTq0dJiUQCq1qqA9u5ulA7ozT4tK9QvncSRRpdtpdGKN3E5fEEpRuoiM7Q3489/QA=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Column1 = _t ]
        ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each
                let
                    SplitText1 = 
                        Splitter.SplitTextByCharacterTransition ( 
                            { "," }, { "A" .. "Z" } 
                        ) ([Column1]),
                    VendorId = SplitText1{0},
                    VendorName = SplitText1{1},
                    RemoveVendorNameID = List.RemoveFirstN ( SplitText1, 2 ),
                    FxRemoveLastComma = 
                        ( Value as text ) as text =>
                            if Text.End ( Value, 1 ) = "," 
                            then 
                                Text.Combine ( 
                                    List.RemoveLastN ( 
                                        Text.ToList ( Value ), 
                                        1 
                                    ) 
                                )
                            else Value,
                    RemoveLastComma = 
                        List.Transform (
                            RemoveVendorNameID,
                            each FxRemoveLastComma ( _ )
                        ),
                    SplitText2 = 
                        List.Transform (
                            RemoveLastComma,
                            each Text.Split ( Text.Trim ( _ ), "," )
                        ),
                    Combine = List.Combine ( SplitText2 ),
                    Check =
                        if List.Contains ( Combine, "PO_ID" ) 
                        then List.Select ( Combine, each _ <> "" )
                        else Combine,
                    Result = { VendorId } & { VendorName } & Check,
                    RemoveLastComma2 = List.Transform ( Result, each FxRemoveLastComma ( _ ) )
                in
                    RemoveLastComma2
        ),
    ToTable = Table.PromoteHeaders ( Table.FromRows ( AddedCustom[Custom] ) ),
    ChangedType = 
        Table.TransformColumnTypes (
            ToTable,
            {
                { "VENDOR_ID", Int64.Type },
                { "VENDOR_NAME", type text },
                { "PO_ID", type text },
                { "PO_DATE", type date },
                { "PO_LINE_COUNT", Int64.Type },
                { "SENT_VIA_EDI", type text },
                { "855_TRANSACTION_COUNT", Int64.Type },
                { "MIN_855_DATE", type datetime },
                { "753_TRANSACTION_COUNT", type any },
                { "MIN_753_DATE", type any },
                { "856_TRANSACTION_COUNT", Int64.Type },
                { "MIN_856_DATE", type datetime },
                { "810_TRANSACTION_COUNT", Int64.Type },
                { "MIN_810_DATE", type date }
            }
        )
in
    ChangedType

[nschmidt]

Below is example of what the data looks like with the row in red representing a vendor with a comma in it's name (BRADY WORLDWIDE, INC.) 

VENDOR_ID,VENDOR_NAME,PO_ID,PO_DATE,PO_LINE_COUNT,SENT_VIA_EDI,855_TRANSACTION_COUNT,MIN_855_DATE,753_TRANSACTION_COUNT,MIN_753_DATE,856_TRANSACTION_COUNT,MIN_856_DATE,810_TRANSACTION_COUNT,MIN_810_DATE

138974,HEICO-LOCK,SMPA15924,10/3/2022,1,Yes,,,,,1,10/03/2022 09:02 AM,1,10/13/2022

131702,DIXON VALVE & COUPLING,LSNY1391,10/3/2022,1,Yes,1,10/03/2022 01:40 PM,,,1,10/03/2022 10:01 AM,1,10/4/2022

131930,SUREWERX USA INC,CN1324317,10/3/2022,1,Yes,1,10/03/2022 01:44 PM,,,1,10/03/2022 11:02 AM,1,10/5/2022

131702,DIXON VALVE & COUPLING,HGMS0281,10/1/2022,1,Yes,1,10/01/2022 02:24 PM,,,1,10/03/2022 12:02 PM,1,10/4/2022

132248,BRADY WORLDWIDE, INC.,MZVA14961,10/3/2022,2,Yes,2,10/03/2022 01:07 PM,,,2,10/03/2022 12:46 PM,1,10/3/2022

[AntrikshSharma]

@nschmidt Share sample data.