cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FredLEGUEN Regular Visitor
Regular Visitor

Return the last user who has modified an Excel file

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Return the last user who has modified an Excel file

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Return the last user who has modified an Excel file

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FredLEGUEN Regular Visitor
Regular Visitor

Re: Return the last user who has modified an Excel file

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

Super User
Super User

Re: Return the last user who has modified an Excel file

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

FredLEGUEN Regular Visitor
Regular Visitor

Re: Return the last user who has modified an Excel file

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

 

Thanks and congratulation for this impressive work.

Super User
Super User

Re: Return the last user who has modified an Excel file

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,335)