Forum Discussion
trying to extract file type from file path
given the path of
| affinity/Silver/active_investors/person/map/In/2020/12/12/persons_Bob R. Joe_2020-12-12.json |
Extracting after the after the delmiter using "." will work on file paths that don't have another "." in them. How can I get the text only after the last "."?
Hi Anonymous - use this formula:
Text.AfterDelimiter([Path], ".", {0, RelativePosition.FromEnd})It will find the first period from the right (end) of the path, then give you all text after that period. Full M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JclBCoAgEADAr4j33NondKtjHSPEYoWNckNF6PcZwdxmWbTzngPnB2Y+C0Vwe+ZClkOhlCUmuCkmCXC5G4YA2GILHX7+SLaXTU1GjUL226bDyhz19Lq+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t]), #"Added Custom" = Table.AddColumn(Source, "Extension", each Text.AfterDelimiter([Path], ".", {0, RelativePosition.FromEnd})) in #"Added Custom"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
10 Replies
Hi Anonymous - use this formula:
Text.AfterDelimiter([Path], ".", {0, RelativePosition.FromEnd})It will find the first period from the right (end) of the path, then give you all text after that period. Full M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JclBCoAgEADAr4j33NondKtjHSPEYoWNckNF6PcZwdxmWbTzngPnB2Y+C0Vwe+ZClkOhlCUmuCkmCXC5G4YA2GILHX7+SLaXTU1GjUL226bDyhz19Lq+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t]), #"Added Custom" = Table.AddColumn(Source, "Extension", each Text.AfterDelimiter([Path], ".", {0, RelativePosition.FromEnd})) in #"Added Custom"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.Yes. Did you try it? It will work if there are 1,000 periods in the path. It will always take the text after the last period.
Great Anonymous - can you please give a thumbs up to any posts that helped and mark one or more as the solution so others know this thread is closed?
Glad I was able to help.Hi Anonymous ,
If the file path is stored as a column, the below approach can be taken:
You can do that in Power Query by right clicking on the column and splitting based on a delimiter. It will then give you an option to select the specific delimiter(in your case it will be ".") and select the option of right most delimeter.
The corresponding M query as below:
= Table.SplitColumn(#"Split Column by Position", "File path", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"File Path", "File Extension"})
Please let me know if it helps.
Glad to help Anonymous
If you have more detailed or specific questions on this, start a new thread, but provide some comprehensive sample data with expected results so we can try to handle all possibilities at once.How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
