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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalpana
Helper I
Helper I

Need help

Hi

 

In a particular folder, i want to merge all files(txt files) from sharepoint. i got it for merging all files. but the thing is i want to filter the file if the particular word is present inside the file. is it possible.

 

and the other thing , i want to get all file names and file created date from the particular folder.

 

 

15 REPLIES 15
MarcelBeug
Community Champion
Community Champion

Since the November 2016 upgrade of Power BI, when you use "Combine Binaries" to expand your column with Binaries (the files from your folder), you get 4 grouped objects and 4 added steps to your code,

 

One of the objects is an example query "Transform Sample Binary from <name of your query>": you can modify this query to filter for your particular word. Any modifications to this query will be automatically applied to the function "Transform Binary from <name of your query>", meaning that the filtering will be applied to each of your files.

 

One of the steps added to your query is "Removed Other Columns1" (or similar). You can modify this step (using the small wheel right of the step name) to select other colums, like file cretaed date. The file name is already included by default.

Specializing in Power Query Formula Language (M)

I dnt want to filter particular row in the file. i want to filter that file if that particular word is there inside the file

If you apply your filter to the example query and the word is not found, then the result will be an empty table.

If the word is found then you want to return the entire table, so you can add a step in your example query like:

Result = if Table.RowCount(#"Filtered Rows") = 0 then #"Filtered Rows" else Source

where Source is the step prior to the filter step.

 

Consequently, you will get null values for empty tables in your combined table (in your master query) after expanding.

There you can add a step to filter out the null values.

Specializing in Power Query Formula Language (M)

I meant, if the word is found in the file, i dnt want that file not a particular row in the file.

 

Thanks for confirming I understood correctly.

Watch this video for better explanation.

It reads files from a folder (rather than Sharepoint, but the concept is the same) and returns only files containing the keyword.

Specializing in Power Query Formula Language (M)

Thank you very much Marcel

 

I need one more help. There are two tables . i want to do lookup to get the values from other table .

 

Need formula for lookup with condition.

 

I tried this = Lookupvalue(result column name, search column name, search value ) but here i want to put condition,

 

Is it possible.?

Hi @kalpana,

 

Thanks for detail description. As the @MarcelBeug pested, please open a new thread. In the forum, there is one issue in one thread. And your second question is not related to the first one. If you ask some problem about, you'd better post sample data and expected result. Thanks for understanding.

 

Best Regards,
Angelia

Doesn;t look like (standard) Power Query to me, so I don't think I can help you.

 

If it isPower Query, then I need more clarification, because I don't understand exactly what you are looking for.

 

Otherwise I would advise to raise a new topic with a descriptive title (not "Need Help"), something like "How to perform a conditonal lookup in DAX".

Specializing in Power Query Formula Language (M)

Capture.PNG

This one will list the name of files and created date. from this, I want to subtract 1 from date created . but i cannot change this even to date (data type) also. when i am subtracting it is giving error

Hi @kalpana,

you can make a new column of "Date" type which will have the substracted date; via the following:

New Column = [Date Created] - 1

See if this meets your requirement.

 

For data type conversion on your coulmn "Date Created", check out the following links:

  1. https://community.powerbi.com/t5/Integrations-with-Files-and/Date-Display-Formats/td-p/2145
  2. https://msdn.microsoft.com/en-us/library/mt253512.aspx

Hope it helps.

i tried this formula only i am getting error

 

Hi @kalpana,

 

You want to subtract 1 day from date created, right? If it is, please try the following formula and check if it works fine.

 

New Column = DATE(YEAR([Date Created]),MONTH([Date Created]),(DAY([Date Created]) - 1))


Best Regards,
Angelia

I Checked it, it s not working fine. i am getting error that date is not recognized

Capture.PNG

In Data also, Date created is showing empty. 

Ok Thanks:)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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