I need to be able to filter on Section, Township Range. The tax roll data concatenates the properties by owner rather than have a separate row or record for each property. How in the world can you create separate records for each instance Sec-Twp-Rng?
I was hoping I could extract the strings with form at nn-nn-nn (e.g. 01-29-63). I started my attempt trying to use wildcard ??-??-?? but that brought no joy.
This is such a jumbled mess it may be impossible.
01-29-63 LOT 01-02-03-04, S/2N/2, S/2 02-29-63 LOT 01-02, S/2NE, SE 04-29-63 S/2SW 05-29-63 SWNE,NWSW PART, NESW, S/2SW, W/2SE, SESE 06-29-63 N/2SE PART,S/2SE 07-29-63 E/2 08-29-63 N/2NE, SWNE, W/2, SE 09-29-63 W/2NE, SENE, W/2, SE 10-29-63 SWNW, S/2 11-29-63 W/2NE, SENE, E/2SE, SW, W/2SE 12-29-63 N/2, N/2SW, SWSW, SE 13-29-63 NE PART, N/2NW, S/2NW PART, SE PART 14-29-63 N/2NE, SWNE, W/2, NWSE, SESE 15-29-63 W/2NE, SENE, W/2, SE 18-29-63 N/2NE [5,886.88A] |
Hi Robert,
Please provide an Excel file containing your sample data before and how you'd like it to look. I can't tell if that data is in one cell and if so, how do you want it to look? It's important that your sample data contains enough examples that every scenario is covered.
Thanks,
Mynda
Thank you Mynda. I tried to use a complex delimiter using numbers and symbols as a way to parse. I searched M for wildcards but couldn't find any.
The data, as I received it, jumbles property descriptions. The delimiter is Section-Township-Range in the form of nn-nn-nn The attached file has the original data with an index column added. A separate sheet illustrates the desired outcome.
Robert
Hi Robert,
The file didn't upload. After selecting the file, you need to click the yellow 'start upload' button.
Mynda
Thank you Mynda. I think my file size was too large. I have abridged the data.
Error 106 - Upload file size exceeds maximum allowed size
Mynda:
I am both honored and grateful that you have chosen to address my dilemma. My approach was to try and create a multiple letter delimiter. As far as I could tell, the wildcards in M do not distinguish between letters and digits.
I look forward to seeing your solution No doubt, others face analogous scenarios.
Best regards,
Robert Cline
Hi Robert,
There is no pattern matching in Power Query yet unfortunately.
But you can build your custom pattern matching, like I did in the attached file.
What I did:
First, I created a list of items to be replaced, in this case we will replace all digits:
List1 = List.Transform({0..9}, each Text.From(_)),
Next, for each digit in List1, we need to provide a replacement. I created then a list of replacements for each item in List1 using: List.Repeat({1},List.Count(List1)). So each digit will be replaced with 1 (you can replace with any text).
ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),
In the final step, I add a new column, replacing all digits from Legal column with 1
Using Text.PositionOf ..., "11-11-11",Occurence.all) we identify the starting position of all matches for "11-11-11" pattern (any digit was replaced with 1, so our pattern is extremely simple to use now). With the list of positions provided, we simply extract from Legal column 8 chars, for each match: Text.Range(x[Legal],_,8)) . In the final step, combine all results using ";" as separator.
Here are all steps combined into 1:
#"Added Custom" = Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") ),
With the list of matches in this new column, all you have to do is to split this column into rows by ";" delimiter (advanced options: split into rows instead of columns)
Ingenious, Catalin!
Dang Catalin. This is simply awesome. You are an M wizard.
Thank you!
Robert Cline
Catalin:
I still don't get it. I get an error "The name 'ReplacementList' wasn't recognized. I get a new column but it doesn't know the type. I tried changing it to as text but there was no change.
= Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),"ReplacementsList"),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") )
I added Quotes to "ReplacementsList" and the code runs but get a column of errors even if I change the type to 'as text'
Do you see the error?
Thank you.
Robert
Hi Robert,
The name ReplacementList was not recognized where? In the sample file I sent, if you read the query, you will see it, open the advanced editor:
let
Source = Excel.CurrentWorkbook(){[Name="Goshen"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account #", type text}, {"Parcel #", Int64.Type}, {"Acct Status", type text}, {"Account Type", type text}, {"Tax District", Int64.Type}, {"Name 1", type text}, {"Name 2", type text}, {"Address 1", type text}, {"Address 2", type text}, {"City", type text}, {"State", type text}, {"Zip Code", Int64.Type}, {"Legal", type text}, {"Gross Acres", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
List1 = List.Transform({0..9}, each Text.From(_)),
ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),
#"Added Custom" = Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") ),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Index", "Account #", "Parcel #", "Acct Status", "Account Type", "Tax District", "Name 1", "Name 2", "Address 1", "Address 2", "City", "State", "Zip Code", "Legal", "Gross Acres"})
in
#"Reordered Columns"
If it's not in your query, you should add it, copy the red lines from above into your query, before adding the new column.
Got it... thank you Catalin!
Robert