Return the last user who has modified an Excel file

[FredLEGUEN]

Hi,

Is there a way to collect the name or the email of the last user who has modifed an Excel file.

When I import many Excel files from a folder, I don't find this information in the Record option.

Do you know if I can do that and how ?

Thanks

[v-piga-msft]

Hi @FredLEGUEN ,

I have a little confused about your scenario.

It seems that you want to return the last user's name and email who has modified the excel.

What does your data sample look like? And what is your data source?

Do you have the record that people modified the excel?

Best  Regards,

Cherry

[FredLEGUEN]

Hi @v-piga-msft 

The scenario is simple.

Each day I generate reports for different shops. Each files contain a list of items to be checked by each manager of the shop

So, when I collect the files each week, I would like to add who has worked on the file. This is why I ask that.

Today, I do it with VBA ActiveWorkbook.BuiltinDocumentProperties("Last author") but I would like to know if it's possible to do it with Power Query

Thanks

[ImkeF · Accepted solution]

Hi Frédéric, to my knowledge you have to go down the rabbit hole to retrieve this information.

This function does that for you 😉

(FileContent as binary) =>
let
    fnUnzip = let
                Source = (ZIPFile) => 
            let
                Header = BinaryFormat.Record([
                    MiscHeader = BinaryFormat.Binary(14),
                    BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)    
                ]),

                HeaderChoice = BinaryFormat.Choice(
                    BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
                        then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
                        else BinaryFormat.Choice(
                                BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                                each BinaryFormat.Record([
                                    IsValid  = true,
                                    Filename = BinaryFormat.Text(Header(_)[FileNameLen]), 
                                    Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]), 
                                    Content  = BinaryFormat.Transform(
                                        BinaryFormat.Binary(Header(_)[BinarySize]),
                                        (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                                    )
                                    ]),
                                    type binary                   // enable streaming
                            )
                ),

                ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),

                Entries = List.Transform(
                    List.RemoveLastN( ZipFormat(ZIPFile), 1),
                    (e) => [FileName = e[Filename], Content = e[Content] ]
                )
            in
                Table.FromRecords(Entries)
            in
                Source,
   
    Custom1 = fnUnzip(FileContent),
    #"Filtered Rows" = Table.SelectRows(Custom1, each ([FileName] = "docProps/core.xml")),
    Content1 = #"Filtered Rows"{0}[Content],
    Custom2 = Lines.FromBinary(Content1){1},
    Custom3 = Text.BetweenDelimiters(Custom2, "<cp:lastModifiedBy>", "</cp")
in
    Custom3

 Simply add a column where you call this function with the [Content]-column as input parameter.

[Anonymous]

Hi @ImkeF, 

Could you please elaborate on "Simply add a column where you call this function with the [Content]-column as input parameter." by sharing a printscreen or example?

I'm looking for a way to obtain the modified by name for all of the excel workbooks in a certain folder.

Thanks in advance,
Ruben

[Anonymous]

Hi,

I have an Excel spreadsheet that I'm trying to get the "Last User" information for.  I'm unsure about what goes into the "FileContent" parameter.  Is it the <file path + file name> or something like Files.Content(<file path + file name>).  I'm a newbie to PBI so I'm not sure.

Thanks in advance!

[ImkeF]

Hello @Anonymous ,
Check out this video: Transform a query into a function in Power Query and Power BI - YouTube

I'm applying this function the column "Description" as an input parameter in there.

[FredLEGUEN]

@ImkeF how have you done this function 😮😲
I'm more than impress.

Thanks and congratulation for this impressive work.

[ImkeF]

Thanks  @FredLEGUEN  😉

you can read a bit more about it here: https://www.thebiccountant.com/2017/08/18/how-to-import-from-excel-with-cell-coordinates-in-power-qu...

The fnunzip-Function comes from Mark White.

Once you've unzipped your xlsx you "just" have to navigate through the indivieual html or xmls.

I usually unzip the xlsx before and use a text editor to find what I'm looking fore before going for it by Power Query.