Why text is changed even when parsed as text

[stej]

I try to parse IIS log files. The log files have this structure: 

 

I load the files from folder, so I combine the files like this

I select "space" as separator, the line is parsed, but the first column is changed to other format. Why?? 

The resulting column has type "text", so why is it interpreted as date?

I tried to change my regional settings in PowerBi Desktop, but no luck.

And other weird behaviour is when I try to merge the first two columns and make a text date like this

click on "Merge column", specify "Space" separator and the result is..

The seconds are lost. Whe I look into Advanced editor, I see

    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Table Column1", {{"Column1", type text}, {"Column2", type text}}, "cs-CZ"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"dt")

It's probably somehow related to the cs-CZ culture (where did it came from?), but this imho doesn't explain, why the seconds are lost.

Thank you for your attention 🙂 Any help would be appreciated as this are the basics that I'm struggling with and I can't move over it.

[Vvelarde · Accepted solution]

@stej

After you Apply the Combine Binaries. In Queries Panel you will find a Folder Name Sample Query and inside of this a Query with the Name Transform Sample Binary From "Your Folder". Select this and go to Advance Editor and change the type date to text.

[v-shex-msft]

Hi @stej,

I'd like to suggest you save the iis log file to csv, then load csv file to query editor.

Then you can choose the specific type of column at "change type" steps.

Full query:

let
    Source = Csv.Document(File.Contents("C:\Users\xxxxx\Desktop\Log.csv"),15,"",null,1252),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type time}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}})
in
    #"Changed Type"

Regards,

Xiaoxin Sheng

[stej]

@v-shex-msft yes, that might work probably, But how do I combine more files from the folder together? Note that I don't know their name and count, just the folder path.

[v-shex-msft · Accepted solution]

Hi @stej,

You can try to use below formula which used to loop the specified folder and combine the file to one table.

let
    LoadCSVFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),"Custom"),
        Combine= Table.Combine(Custom[Custom])
in
    Combine
in 
    LoadCSVFileAndCombine

Use steps:

1. Open query editor

2. Add blank query.

3. Open the advanced editor.

4. Paste above formula.

5. Use folder path to invoke above function.

Regards,

Xiaoxin Sheng

[stej]

@v-shex-msft

That also did the trick. I had to change it a little bit and give it custom separator, so the result is this (pasing here mainly for me 😉

let
    LoadCSVFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".log"),
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content], null, " ")),"Custom"),
        Combine= Table.Combine(Custom[Custom]),
        #"Renamed Columns" = Table.RenameColumns(Combine,{{"Column1", "date" }, {"Column2", "time" }, {"Column3", "s-sitename" }, {"Column4", "s-computername" }, {"Column5", "s-ip" }, {"Column6", "cs-method" }, {"Column7", "cs-uri-stem" }, {"Column8", "cs-uri-query" }, {"Column9", "s-port" }, {"Column10", "cs-username" }, {"Column11", "c-ip" }, {"Column12", "cs-version" }, {"Column13", "cs(User-Agent)" }, {"Column14", "cs(Cookie)" }, {"Column15", "cs(Referer)" }, {"Column16", "cs-host" }, {"Column17", "sc-status" }, {"Column18", "sc-substatus" }, {"Column19", "sc-win32-status" }, {"Column20", "sc-bytes" }, {"Column21", "cs-bytes" }, {"Column22", "time-taken" }})
    in
        #"Renamed Columns"
in 
    LoadCSVFileAndCombine

Thanks, accepted.

[Vvelarde · Accepted solution]

@stej

After you Apply the Combine Binaries. In Queries Panel you will find a Folder Name Sample Query and inside of this a Query with the Name Transform Sample Binary From "Your Folder". Select this and go to Advance Editor and change the type date to text.

[mike_honey]

Please post your entire script from the Advanced Editor.

[stej]

The script looks like this

let
    Source = Folder.Files("d:\powerbi-test\iis\transformed"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform Binary from transformed", each #"Transform Binary from transformed"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform Binary from transformed"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Binary from transformed", Table.ColumnNames(#"Removed Other Columns1"[#"Transform Binary from transformed"]{0}))
in
    #"Expanded Table Column1"

and the result

[mike_honey]

That looks like a different function - there's no "Merged Columns" step?

[stej]

@mike_honey This was without the merge step. 

Wen I apply "Merged columns", the result looks like this:

And the query is 

let
    Source = Folder.Files("d:\powerbi-test\iis2\transformed"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform Binary from transformed", each #"Transform Binary from transformed"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform Binary from transformed"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Binary from transformed", Table.ColumnNames(#"Removed Other Columns1"[#"Transform Binary from transformed"]{0})),
    #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Table Column1",{"Column1", "Column2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Other Columns2", {{"Column1", type text}, {"Column2", type text}}, "cs-CZ"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"