Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

 

edhans_0-1610487047893.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

10 REPLIES 10
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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.

 

edhans_0-1610487047893.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

 

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

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.

edhans_0-1610491564925.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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. 

edhans_0-1610498343594.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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
Community Champion
Community Champion

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
Not applicable

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))

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors