Forum Discussion
Reverse text string lookup between delimiters
Hi Folks, I'm somewhat of a newbie that requires your help/direction. I have searched the forums but cannot find a similar issue and resolution. I have been grappling with this but just cannot resolve it. I have text string of various lengths and need to extract the value between the 1st and second commas from the right (reverse). The value is always 7 characters in lenght. Please see the sample below (I need to extract the bolded values - 2 position from the right):
X053469,X051230,X000316,X000300,X000010,X000001
X051970,X005354,X006353,X000010,X000001
X050467,A005319,X003276,X011638,X000042,X000007,X000001
X050358,X000231,X011638,X000042,X000007,X000001
X003908,X006353,X000010,X000001
Any assistance or guidance would be greatly appreciated.
Anonymous
This query reads the csv in and adds the column "New column" at the end of the table with the required content. It just follows the steps I described earlier:
let Source = Csv.Document(File.Contents("d:\YOUR_PATH_HERE\Sample org hierarchy file.csv"),[Delimiter=",", Columns=39, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "New column", each List.LastN(Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Department Hierarchy]),2){0}) in #"Added Custom"You'll have to update the path for the file with the actual path tou your data (in the first step--> File.Contents("d:\YOUR_PATH_HERE\Sample org hierarchy file.csv")
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
9 Replies
Hi Anonymous
You can do this easily in PQ. Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BDoAgDAT/wpnDLktbOfoLE8P/v6GIN0PiaVIyU3qe6YCpess3WYSbgOiTmDP4Ekw9Pw1bPG8mq4Mu08pF9cj7cDn+gUqM/aRrm24tbxOfVjadIv5soIZtfVO/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Column1])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.LastN([Custom],2){0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}) in #"Removed Columns"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous
with Power Query you can do it like this:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BDoAgDAT/wpnDLktbOfoLE8P/v6GIN0PiaVIyU3qe6YCpess3WYSbgOiTmDP4Ekw9Pw1bPG8mq4Mu08pF9cj7cDn+gUqM/aRrm24tbxOfVjadIv5soIZtfVO/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.AfterDelimiter(_, ",", {1, RelativePosition.FromEnd}), type text}}), #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1", each Text.BeforeDelimiter(_, ","), type text}}) in #"Extracted Text Before Delimiter1"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Anonymous
This query reads the csv in and adds the column "New column" at the end of the table with the required content. It just follows the steps I described earlier:
let Source = Csv.Document(File.Contents("d:\YOUR_PATH_HERE\Sample org hierarchy file.csv"),[Delimiter=",", Columns=39, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "New column", each List.LastN(Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Department Hierarchy]),2){0}) in #"Added Custom"You'll have to update the path for the file with the actual path tou your data (in the first step--> File.Contents("d:\YOUR_PATH_HERE\Sample org hierarchy file.csv")
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks so much FrankAT and AlB. Both solutions work well, I do prefer the one proposed by AIB as it creates a new column with the extracted value. I have only dabbled with DAX but not PQ and trying to decipher the code so that I can modify to make it work for me. My table contains numerous columns and thousands of rows. I highlighted the column with the said text and pasted the code in the Advanced Editor and find that it removes all the other data in the table and just returns 5 rows. Sorry if this is a very basic question but any thoughts would be appreciated on how to correct this.
Anonymous
I'd need to see exactly what you are doing to be able to tell where the issue is. If you haven' worked much with M, let's try another approach. For each column you want to extract the values from, create a custom column with this code:
= List.LastN(Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([ColumnName]),2){0}
where ColumName is the name of the column you are processing.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks again for your quick response. This is what I am attempting - I have a table (CSV file) with 25 columns and thousands of rows. One of the columns is called Dept Hierarchy which contains the values of which I need the second from right. Once it is isolated in a seperate column I have a seperate lookup table that will match it to a friendly Dept name i.e. Finance etc. I have tried the Dax formula that you provided but I am getting the following error, I am sure it is simple enough but beyond my skill level. Here is the error:
Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=
Department Hierarchy=A004686,X003053,X000223,X000042,X000007,X000001
Type=[Type]
Anonymous
can you share the csv file? No need for all the rows but it should have all the columns
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
