Filter imported data through Power Query

[Dimuster]

Hi.

I have my Solar generation data exported to excel files which have records throughout the day in 5 minutes intervals. Excel files break in days wise by the solar data software & stored in a folder.

For analyzing & report creation purposes I import these files to an excel through Power query. Sometimes I want to get a particular day's data into an excel sheet. @Peter_SSs gave me a suggestion to extract the required date's data if I have all data in the excel sheet. But rather than importing all the data to an Excel sheet & then filtering, I want to filter the data inside the power query & import only the required data to the excel sheet.

In the excel sheet I have a cell in that I input the date I want. Then I want to power query to read that date & filter only the data that match to the said date & import to the excel sheet.

Please help me to have this done.

I have Office Professional Plus 2019 version in Excel.

 

[jdellasala]

When you open the query, it will look just like an Excel table. You can manually filter the data the same way, and the M Code will be generated automatically, but it will not be dynamic and can only be changed by editing the query.
Alternatively you can create a table of exclusions and then filter out those entries from the column of the table that needs the filtering.

Book1
	A	B	C	D	E
1	Full Name		Exclude		Full Name
2	Darion Evelyn		Madyson Hampton		Darion Evelyn
3	Marina Evans		Lyric Leycester		Marina Evans
4	Madyson Hampton				Meghan Tefft
5	Meghan Tefft				Kali Armitage
6	Adrianna Chatsey				Kaley Jenkins
7	Kali Armitage				Abram Alarcón
8	Kaley Jenkins				Arabella Azevedo
9	Abram Alarcón				Charity Mayo
10	Lyric Leycester				Zain Kingston
11	Arabella Azevedo				Casey Clowes
12	Charity Mayo				Terrence Colburn
13	Zain Kingston				Prince Calder
14	Casey Clowes				Kayli Russell
15	Terrence Colburn				Pierce Jensen
16	Enzo Andreassen
17	Prince Calder
18	Kayli Russell
19	Pierce Jensen
Sheet1

The table on the left is named Names. The table in the middle is named Exclude.
Bring the Exclude table into Power Query and convert it to a list by right clicking on the column and select Drill Down:

let
    Source = Excel.CurrentWorkbook(){[Name="Exclude"]}[Content],
    DrillDown = Source[Exclude]
in
    DrillDown

Steps are manually renamed. I hate that #"" nonsense!
Now pull in the data file. I've included one step using a manual filter, and one using the Exclude list:

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    FilteredSelectedRows = Table.SelectRows(Source, each ([Full Name] <> "Adrianna Chatsey" and [Full Name] <> "Enzo Andreassen")),
    FilteredByList = Table.SelectRows(FilteredSelectedRows, each ( List.Contains( Exclude, [Full Name] ) = false  ))
in
    FilteredByList

You end up with the table on the right. Either way, you should be set.

 

[Dimuster]

Thanks for the answer. But I want to extract the data that match the date. Not to Exclude.

Sorry if I misunderstood.

 

[JGordon11]

With D2 being a named Range name Date_To_Import,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "fZFLCoAwDAWvIl0L5qUfrTsPIHQv3v8aFsViQyJ0N0wnIcfhMAETE/sBvBLVN2y7G12GO8cvTl8KigI3u9x2NuyH8ktZS4MWwUU7WXrp
        J2elDWrYq3vPAnftHCzZ+Fy0/YuDujhIcCMu7SJuEtR4w/H/4FFre0su/WhRu3c92HkB", 
        BinaryEncoding.Base64), Compression.Deflate)), type table [#"Date/Time", Output]),
    tbl = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Output", type number}}),
    dt = Date.From(Excel.CurrentWorkbook(){[Name = "Date_To_Import"]}[Content][Column1]{0}),
    Result = Table.SelectRows(tbl, each Date.From([#"Date/Time"]) = dt)
in
    Result

Book4
	A	B	C	D	E
1	Date/Time	Output		Date To Import
2	1/14/2023 0:00	110		1/14/2023
3	1/14/2023 6:00	94
4	1/14/2023 12:00	105
5	1/14/2023 18:00	105
6
Table