trying to extract file type from file path

[Anonymous]

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 "."?

[edhans]

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.

[edhans]

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.

[edhans · Accepted solution]

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.

[Anonymous]

will this still work if there's another period in the path? ie:

~/persons_Bob.Bobby R.Joe_2020-12-25.json

[edhans]

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.

[Anonymous]

Confirmed, thanks! as an improvevment, are we able to apply this dynamically based on file type? ie: '~.tar.gz'

[edhans]

On 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.

[Anonymous]

I think for the purpsoe of this report, the first above solution should be good enough, thanks for your help on this! much appreciated!

[PC2790]

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.

[Anonymous]

ended up doing in this way:

1. find the last occurence of the [FindLastPeriod] = Table.AddColumn(#"Duplicated Column1", "FindLastPeriod", each Text.PositionOf([Name], ".", Occurrence.Last))
2. then a Text.Middle([Name], [FindLastPeriod], 20))