Seprate Date from text file with numbers

[Freefly]

Hi trying to connect a folder that recives new textfiles with some stats every day.
The data seam to load ok, but I dont manage to separate the date as "Date" so that it become usabel for showing trends etc.
My text file looks like this, 

Date : 12/06/2017
No of Transaction :  1418
ErrorCode                     :         3
System processing error        :         2
Transaction declined                :       37 

I could change text file it, but no clue what works

[Freefly · Accepted solution]

Hi, thanks ok I try to rephrase.

I connect to a folder that will recive an new text file every day.

Stats 171206.txt

Stats 171207.txt

Stats 171208.txt

.......

The content and format in the text file is as above.
I get the data in for all text files, but using your queary above only give the date 6th of Decmeber

I used Get data and connetcted to the folder and the user Combine & Edit
Not sure how you (canceling first row as header), i simply used filters in that column to remove Date.
Then did a dublicate query and replaced the query code with your code in the advanced editor

[MarcelBeug · Accepted solution]

If I read your example files from a folder, then I automatically get a table with 2 columns.

In the example query, I only need to transpose the table and promote headers.

In the main query, only the "Changed Type" needs to be adjusted.

This is the code van de example query:

let
    Source = Csv.Document(#"Sample File Parameter1",[Delimiter=":", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    #"Transposed Table" = Table.Transpose(#"Trimmed Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"No of Transaction", Int64.Type}, {"ErrorCode", Int64.Type}, {"System processing error", Int64.Type}, {"Transaction declined", Int64.Type}})
in
    #"Changed Type"

This is the code from the main query:

let
    Source = Folder.Files("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Freefly"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Freefly", each #"Transform File from Freefly"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Freefly"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Freefly", Table.ColumnNames(#"Transform File from Freefly"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"No of Transaction", Int64.Type}, {"ErrorCode", Int64.Type}, {"System processing error", Int64.Type}, {"Transaction declined", Int64.Type}})
in
    #"Changed Type"

This is how it is created, but after the recording I inserted step "Trimmed Text" in the example query (and recreated the "Changed Type" step in bot queries), to remove trailing spaces from the (future) column names.

[MarcelBeug]

In the Source step of the query below, I pasted your input, using option "Enter Data" in the Query Editor (canceling first row as header).

Next, I created the other steps to only select the date and make it a date.
This will only work from Power BI Desktop version December 2017, in which the function Text.Select was introduced.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVWwUjA00jcw0zcyMDRXitWJVvLLV8hPUwgpSswrTkwuyczPAypRMDQxtADLuhYV5Rc556ekKmADVnCWMVh1cGVxSWquQkFRfnJqcXFmXrpCKkg/pnIjsHJkS1NSk3My81JTcFlhbK6gFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    FirstRow = Table.FirstN(Source,1),
    SelectedText = Table.TransformColumns(FirstRow,{{"Column1", each Text.Select(_,{"0".."9","/"}), type text}}),
    Typed = Table.TransformColumnTypes(SelectedText,{{"Column1", type date}})
in
    Typed

Probably this is not what you are looking for, but it is the best I can offer with the information you supplied.

You might consider rephrasing your question.

[Freefly · Accepted solution]

Hi, thanks ok I try to rephrase.

I connect to a folder that will recive an new text file every day.

Stats 171206.txt

Stats 171207.txt

Stats 171208.txt

.......

The content and format in the text file is as above.
I get the data in for all text files, but using your queary above only give the date 6th of Decmeber

I used Get data and connetcted to the folder and the user Combine & Edit
Not sure how you (canceling first row as header), i simply used filters in that column to remove Date.
Then did a dublicate query and replaced the query code with your code in the advanced editor

[MarcelBeug · Accepted solution]

If I read your example files from a folder, then I automatically get a table with 2 columns.

In the example query, I only need to transpose the table and promote headers.

In the main query, only the "Changed Type" needs to be adjusted.

This is the code van de example query:

let
    Source = Csv.Document(#"Sample File Parameter1",[Delimiter=":", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    #"Transposed Table" = Table.Transpose(#"Trimmed Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"No of Transaction", Int64.Type}, {"ErrorCode", Int64.Type}, {"System processing error", Int64.Type}, {"Transaction declined", Int64.Type}})
in
    #"Changed Type"

This is the code from the main query:

let
    Source = Folder.Files("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Freefly"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Freefly", each #"Transform File from Freefly"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Freefly"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Freefly", Table.ColumnNames(#"Transform File from Freefly"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"No of Transaction", Int64.Type}, {"ErrorCode", Int64.Type}, {"System processing error", Int64.Type}, {"Transaction declined", Int64.Type}})
in
    #"Changed Type"

This is how it is created, but after the recording I inserted step "Trimmed Text" in the example query (and recreated the "Changed Type" step in bot queries), to remove trailing spaces from the (future) column names.

[Freefly · Accepted solution]

Thanks that did the trick, nice video to

Br

Håkan