Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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.
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.
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".
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:
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
In Data also, Date created is showing empty.
Ok Thanks:)
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |