Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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