connecting to xpf files

[Anonymous]

I am trying to automate a connection to a folder of .xpf files that contain daily updates from a machine.  The files can be opened in word or excel, it's really just text output, but I'd like to avoid this step and just pull them directly into Power BI.  I can work with a solution in R or Python if there are any.  Thanks! 

[Anonymous]

@Greg_Deckler 

I did try to do that, replacing the code that is automatically generated with the code you provided, but with "Source= Folder.Files" instead of "Source=Xml.tables" but I get the following error....

I really appreciate your help with this!

[Greg_Deckler · Accepted solution]

@Anonymous Here is my Transform Sample File query

let
    Source = Xml.Tables(Parameter1),
    #"Expanded TEXT" = Table.ExpandTableColumn(Source, "TEXT", {"s", "n"}, {"TEXT.s", "TEXT.n"}),
    #"Expanded TEXT.s" = Table.ExpandTableColumn(#"Expanded TEXT", "TEXT.s", {"Element:Text", "Attribute:HEADLINE", "Attribute:META", "Attribute:VAL", "Attribute:UNDERLINE"}, {"TEXT.s.Element:Text", "TEXT.s.Attribute:HEADLINE", "TEXT.s.Attribute:META", "TEXT.s.Attribute:VAL", "TEXT.s.Attribute:UNDERLINE"})
in
    #"Expanded TEXT.s"

This is the main query:

let
    Source = Folder.Files("c:\temp\xpf"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

[Anonymous]

Thanks Greg!  That worked!

[Anonymous]

@Greg_Deckler That works to connect to a single file.  Do you know how I would modify that to connect to a folder where daily reports will be added?

[Greg_Deckler]

@Anonymous Use a Folder query. It will auto-recognize the file as XML, at least it did in my case. So you point the query to the folder and then choose Combine and Transform. It will come up with one of the files, a sample file and you again want to Transform. A bunch of queries get created and you want to edit the Transform Sample File query. You perform the transformations there and then those get repeated for every file in the folder and they all get appended together.

[Anonymous]

@Greg_Deckler thanks!  I'll see if I can make that work!

[Anonymous]

@Greg_Deckler 

When I open in notepad, I get a lot of extra stuff, so it's clearly not clean text output, needs to be unpacked somehow.  I've tried to use the text and excel file connections, but those don't work.  

Here's one of the files: https://drive.google.com/file/d/1-MCbPxHOYpLSgk8EHWM4JoR_b-UH-67X/view?usp=sharing 

[Greg_Deckler]

@Anonymous OK, that is an XML file. You can use the XML file connector. I was able to do that successfully. XML is a bit of a pain to work with because you will have a bunch of expansions that you will need to do in order to get to the actual data.

Here is an example of expanding that file to get to some data:

let
    Source = Xml.Tables(File.Contents("C:\Users\gdeckler\Downloads\PRT1112_20357.XPF")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:Version", Int64.Type}}),
    #"Expanded TEXT" = Table.ExpandTableColumn(#"Changed Type", "TEXT", {"s", "n"}, {"TEXT.s", "TEXT.n"}),
    #"Expanded TEXT.s" = Table.ExpandTableColumn(#"Expanded TEXT", "TEXT.s", {"Attribute:HEADLINE", "Attribute:META", "Attribute:VAL"}, {"TEXT.s.Attribute:HEADLINE", "TEXT.s.Attribute:META", "TEXT.s.Attribute:VAL"}),
    #"Expanded TEXT.n" = Table.ExpandTableColumn(#"Expanded TEXT.s", "TEXT.n", {"Attribute:META", "Attribute:VAL"}, {"TEXT.n.Attribute:META", "TEXT.n.Attribute:VAL"})
in
    #"Expanded TEXT.n"

[Anonymous]

@Greg_Deckler looks like I can't upload a file, here is a view of part of one of the files as it looks in word.  let me know if there's another way to answer your question

[Greg_Deckler]

@Anonymous So, can you open the file in Notepad? If it truly is a Text file, you should be able to use the Text/CSV connector or if it truly opens in Excel, maybe the Excel connector? Can you share a file on OneDrive or Box or something?

[Greg_Deckler]

@Anonymous Can you post an example of the kind of xpf file you are using? In doing research, there are many different possibilities for the data stored in an XPF file.