Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FredLEGUEN
Helper III
Helper III

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

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!

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

Thanks and congratulation for this impressive work.

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors