Forum Discussion
Solved
Power Query to Remove 2nd and 3rd Comma
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!
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
3 Replies
nschmidt Share sample data.
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 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
