Forum Discussion
Reverse text string lookup between delimiters
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.
Community ChampionAnonymous
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]