Forum Discussion
trying to extract file type from file path
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.
Community ChampionOn the .tar.gz file type, that is a bit different. You could use this formula:
if Text.End([Path], 6) = "tar.gz"
then "tar.gz"
else Text.AfterDelimiter([Path], ".", {0, RelativePosition.FromEnd})I don't know how you could make it dynamically figure out which file extensions are 2 periods and which are 1. If tar.gz is the only one you need with 2, then the above works.
So above. the file name that ends with "was a .pdf.txt" it will properly pull txt.
If you have a series of double.period file extensions, you would probably need to do a list of them and then use List.Contains. I am not aware of a way to get Power Query to go:
Only pull the right most text after the last period unless it is a Unix file name that has 2 periods in it, then pull the left 2. It cannot know what the valid extensions are.
You can dynamically pull the right most by changing the 0 to a 1 in original formula {0, RelativePosition.FromEnd} becomes {1, RelativePosition.FromEnd} - but then it will not correctly pull .json or .txt.
I'd need so see some data if you want to go down that path.
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.
I think for the purpsoe of this report, the first above solution should be good enough, thanks for your help on this! much appreciated!