Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
In my query editor, i have several of files.
The files are naming based on <plant_filename_date>
In the query editor, there are files from the same plant but different date.
Can i have filter in query editor, just for 1 file for the same plant with the lastest date?
Solved! Go to Solution.
You can Group By plant with operation "All Rows" and then adjust the generated code to have the maximum date selected.
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Plant"}, {{"AllData", each Table.Max(_,"Date"), type record}}), #"Expanded AllData" = Table.ExpandRecordColumn(#"Grouped Rows", "AllData", {"FileName", "Date"}, {"FileName", "Date"}) in #"Expanded AllData"
You can Group By plant with operation "All Rows" and then adjust the generated code to have the maximum date selected.
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Plant"}, {{"AllData", each Table.Max(_,"Date"), type record}}), #"Expanded AllData" = Table.ExpandRecordColumn(#"Grouped Rows", "AllData", {"FileName", "Date"}, {"FileName", "Date"}) in #"Expanded AllData"
Hi,
I dont have plant and date column.
What i have just column for source name that contain <plantName_fileName_Date>
for example <F02_Shipping_19062017>
so do i need to extract this information first?
Yes. Propobably the best thing you can do is split the column on each underscore (_) and proceed from there.
Sorry, Could you help me to split for the plant and date? I had tried, but got the error.
Well, if you use DAX code in the query editor, it isn't going to work.
This would be Power Query code:
let Content = Table.FromColumns({{"F02_Shipping_19062017","F02_Shipping_19062018","F03_Shipping_19062017"}},type table[Name = text]), #"Split Column by Delimiter" = Table.SplitColumn(Content, "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}, {"Name.3", Int64.Type}}) in #"Changed Type"
And this is how the last 2 steps were created:
what is file name?
Because i got null value?
So now you are asking me where you can find the filenames?
That feels like the world upside-down.
It is your data, so you should know where the filename is located.
Otherwise please share a screenshot that shows your column with filenames.
Are you sure you selected this column before splitting by underscore (_)?
Or did you just copy and paste my code somewhere into your query?
That ain't gonna work, that's why I created a video to illustrate the step to take.
Ok, i got the solution..!
Sorry and thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |