Power query not importing the corect atributes from windows folder

oscarlimerick · #1 06-26-2024, 07:41 PM

Hello, I just discovered power query and thought I understood it fairly well but I don't. I have attached a pdf of a windows folder (temp locn for mp3 folder song list) that I have on my laptop that contains some MP3 files. (see attached pdf titled "windows folder with name and windows attributes" ) As you can see, there are 2 windows attributes, Album, which contains the author of the song, and Name, which contains the title of the song.

When I do an excel power query and open this folder, power query presents an edit page that does not have these 2 attributes on it. Not only that, but it has other file attributes that I have not asked for but they seem to have been imported into the query The file attributes that power query collected for me were content,name,extension, date accessed, date created, date modified, attributes, and folder path. (See attached pdf titled "output of power query editor") I didn't want any of these in the report, I only wanted author and name!

Am I missing a step in the setup? How do I make power query import the folder and file attributes to my spreadheet that I am intereted in seeing and editing? Thanks

Alansidman · #2 06-27-2024, 02:14 AM

Click on the column containing Binary. Select remove other columns. Then click on the double drop down arrows in the upper right corner.

JackKovalsky · #3 06-27-2024, 05:07 AM

Hi,
To calculate a weighted average in Excel from multiple columns of grades and weights, you can use a straightforward formula. Let’s say your grades are in columns B, D, F, etc., and weights are in columns C, E, G, etc. Here’s what you do: in a new cell where you want the average, type =SUMPRODUCT(B2:F2, C2:G2) / SUM(C2:G2). This formula works by multiplying each grade by its corresponding weight across the columns, summing them up, and dividing by the total sum of weights. If there are empty cells in the weights column that you want to ignore, modify the formula like this: =SUMPRODUCT(B2:F2 * (C2:G2 <> "") * C2:G2) / SUM(C2:G2 * (C2:G2 <> "")). This ensures you get an accurate weighted average even with missing data. Adjust the cell references (like B2:F2 and C2:G2) to match your actual data layout, and you’re good to go!

Jack

oscarlimerick · #4 06-27-2024, 08:36 AM

Quote:

Originally Posted by Alansidman

Click on the column containing Binary. Select remove other columns. Then click on the double drop down arrows in the upper right corner.

But the column containing binary only shows up in the initial window after I select my folder (see attached pdf titled "initial edit window"). From here there is no way to click on the column containing binary. And when I click either load or edit from here it presents an editable spreadsheet or edit workfield, but the content column with the values that state “binary” is missing from this.

oscarlimerick · #5 06-27-2024, 08:42 AM

But the column containing binary only shows up in the initial window after I select my folder (see attached pdf titled "initial edit window"). From here there is no way to click on the column containing binary. And when I click either load or edit from here it presents an editable spreadsheet or edit workfield, but the content column with the values that state “binary” is missing from this.

Alansidman · #6 06-27-2024, 10:41 AM

Cannot manipulate data in a picture. Actual file would be beneficial. Still seeing Binary Field. Do not understand your issue.

oscarlimerick · #7 06-27-2024, 11:01 AM

Quote:

Originally Posted by Alansidman

Cannot manipulate data in a picture. Actual file would be beneficial. Still seeing Binary Field. Do not understand your issue.

Ok, understood, but the actual file is an mp3 file which I cant attach to this forum, and likewise I cant attach the windows folder, so how do I get this to you? I too sdee the binary field but oinly on the initial edit screen, once I click on either load or edit, it disappears, and on the initial window it does not allow me to click on the binary fields or otherwise manipulate them. Thanks

Pecoflyer · #8 06-27-2024, 11:47 PM

Try zipping the necessary folders and download the zip file to the board

oscarlimerick · #9 06-28-2024, 12:24 AM

When I try to upload the 7 zip windows folder that contains the files I want to export to a spreadsheet, I get an error message from the microsoft excel forum page saying "your submission could not be processed because a security token was missing"

Pecoflyer · #10 06-28-2024, 07:03 AM

Then try a rar format

oscarlimerick · #11 06-28-2024, 11:00 AM

Looking over this thread , I think I have done a poor job of explaining what I am trying to do. I have a windows folder that contains some mp3 files. Using windows and going into this folder I can select certain metatdata properties from the files. I want to have the folder show title, contributing artist, file size, Album, Author, and things like this.

I can select the metadata properties I want and they show up in the windows folder. Now I want to take the windows folder, with the existing selected metadata propeeties, and export that to a spreadsheet, and I want the spreadsheet to show all the metadata properties I have selected organized into columns that can be sorted or otherwise changed. . When trying to use power query to do this, my output of the wiondows folder only contains certain metadata fields, not all the ones I have selected, and some I haven't selected. So either I am using power query wrong, or power query does not do this for me, in which case maybe I need a different program.

Hopefully my new explanation makes more sense. Thanks again all

Alansidman · #12 06-28-2024, 08:32 PM

Here is some VBA to get you on to the right track. You may have to do some research on how to get the file detail fields to complete. But I think what you want is something that you need to use VBA.

Code:

Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Pecoflyer · #13 06-30-2024, 07:21 AM

There is also quite some software available to extract metadata one way or another

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Power Query Formatted Text	gmaxey	Excel Programming	4	11-18-2023 10:00 PM
Excel power query help	ganesang	Excel Programming	0	09-20-2021 01:50 AM
Format numbers in Power Query	Marcia	Excel	2	09-21-2020 12:59 AM
Do I need a power query for this one?	shabbaranks	Excel	5	04-10-2019 03:29 AM
Query and Power Query not working Excel 2016	bl10	Excel	0	07-22-2016 06:25 PM