Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
Anonymous
Not applicable

open file as text

‎05-03-2019 01:45 AM

Hello,

 

Getting the "We didn't recognize the format of your first file" error when trying to extract the binaries.

Tried to link to the file directly and that didn't work either.

 

Any ideas on how to access it so to be able to model it?

 

File link

 

Thank you

J

Labels:
Message 1 of 9
4,995 Views
0
3 ACCEPTED SOLUTIONS
artemus
Microsoft Employee
Microsoft Employee

‎05-03-2019 07:27 AM

Here you go. Just change the Source line to where you got the data

// B879-MCK-MARRIOTT-CENTRAL-2018
let
    Source = Text.FromBinary(File.Contents("C:\Users\artemus\Downloads\B879-MCK-MARRIOTT-CENTRAL-2018.slog")),
    #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"),
    #"Removed Top Items" = List.Skip(#"Split Text",1),
    #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}})
in
    #"Renamed Columns3"

View solution in original post

Message 2 of 9
4,973 Views
artemus
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-03-2019 09:05 AM

Here is one that should work (I think). Just replace the path in the Download query

// SlogParser
(file as binary) =>
let
    Source = Text.FromBinary(file),
    #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"),
    #"Removed Top Items" = List.Skip(#"Split Text",1),
    #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}})
in
    #"Renamed Columns3"

// Downloads
let
    Source = Folder.Files("C:\Users\artemus\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".slog")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "SlogTable", each SlogParser([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"SlogTable"}),
    #"Expanded SlogTable" = Table.ExpandTableColumn(#"Removed Other Columns", "SlogTable", {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}, {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"})
in
    #"Expanded SlogTable"

View solution in original post

Message 4 of 9
4,955 Views
0
artemus
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-07-2019 03:53 AM

1. Rename the function query to SlogParser

2. The function query is intermediate. Just leave it alone, it won't output any table by itself.

View solution in original post

Message 8 of 9
4,836 Views
8 REPLIES 8
artemus
Microsoft Employee
Microsoft Employee

‎05-03-2019 07:27 AM

Here you go. Just change the Source line to where you got the data

// B879-MCK-MARRIOTT-CENTRAL-2018
let
    Source = Text.FromBinary(File.Contents("C:\Users\artemus\Downloads\B879-MCK-MARRIOTT-CENTRAL-2018.slog")),
    #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"),
    #"Removed Top Items" = List.Skip(#"Split Text",1),
    #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}})
in
    #"Renamed Columns3"
Message 2 of 9
4,974 Views
Anonymous
Not applicable
In response to artemus

‎05-03-2019 07:51 AM

Thank you @artemus.

Works a treat! Smiley Happy

I am keen to learn how you did it.

 

It looks that if I want to run it for different files I will always need to edit the query in the Advanced Editor since it looks that the Source does not accept parameters.

 

In my case I will have multiple slog files saved in multiple folders and sub-folders. Is there a way so that I can combine the binaries as for other text file formats?

Message 3 of 9
4,970 Views
0
artemus
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-03-2019 09:05 AM

Here is one that should work (I think). Just replace the path in the Download query

// SlogParser
(file as binary) =>
let
    Source = Text.FromBinary(file),
    #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"),
    #"Removed Top Items" = List.Skip(#"Split Text",1),
    #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}})
in
    #"Renamed Columns3"

// Downloads
let
    Source = Folder.Files("C:\Users\artemus\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".slog")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "SlogTable", each SlogParser([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"SlogTable"}),
    #"Expanded SlogTable" = Table.ExpandTableColumn(#"Removed Other Columns", "SlogTable", {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}, {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"})
in
    #"Expanded SlogTable"
Message 4 of 9
4,956 Views
0
Anonymous
Not applicable
In response to artemus

‎05-03-2019 09:29 AM

@artemus 

I am getting an error of 

 

"Expression.SyntaxError: Token Eof expected."

 

 

Message 5 of 9
4,951 Views
0
artemus
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-03-2019 11:03 AM

This is 2 queries. The // line is the name of the query

Message 6 of 9
4,940 Views
0
Anonymous
Not applicable
In response to artemus

‎05-07-2019 03:36 AM

So, I created 2 new blank queries and copy/pasted the different bits of text.

 

I am now getting the following error for the Downloads query.

 

Expression.Error: The import SlogParser matches no exports. Did you miss a module reference?

 

And the (file as binary) is asking me to enter a parameter. See image below.

 

Image 1169.png

 

Message 7 of 9
4,843 Views
0
artemus
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-07-2019 03:53 AM

1. Rename the function query to SlogParser

2. The function query is intermediate. Just leave it alone, it won't output any table by itself.

Message 8 of 9
4,837 Views
Anonymous
Not applicable
In response to artemus

‎05-07-2019 03:59 AM

Thank you very much @artemus.

It works as a charm.Smiley Happy

Message 9 of 9
4,830 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Kudoed Authors
View All