none
How to format below data using powerquery[Help Needed] RRS feed

  • Question

  • Hi Please guide me in formatting below data using Powerquery:

    Also the data columns should like Commit Merge Author date

    commit 10fe86f274cf032f748ebe884ff669161d9c2b53
    Merge: 6c4df88 5b6fe22
    Author: manish <manish.jha@paytm.com>
    Date:   Mon Oct 16 12:20:09 2017 +0530

        Merge branch 'dev_integration_sprint10.1' of bitbucket.org:paytmteam/bo_web_services into PGP-3024
    commit 5b6fe2237326318f7932adad6139f49d976aba4c
    Merge: 6b458a7 4008f3a
    Author: pavankum <pavan.kumar@paytm.com>
    Date:   Mon Oct 16 06:23:03 2017 +0000
        Merged in PGP-3891 (pull request #543)
       
        [PGP-3891]changing log level of requests
       
        Approved-by: honeyduhar <honey.duhar@paytm.com>
        Approved-by: saurav kachhwaha <saurav.kachhwaha@paytm.com>
    commit 6c4df88aba0866e2e6301e93604574d6056a02de
    Author: manish <manish.jha@paytm.com>
    Date:   Mon Oct 16 06:58:06 2017 +0530
        [PGP-3024] : chnages to show Auth code column in recon view tab
    commit 4008f3a9b11aedfe8060940ccaffd41a5f77fa76
    Author: PAVAN KUMAR <pavan.kumar@197NODMB16106.local>
    Date:   Fri Oct 13 15:04:33 2017 +0530


    Tuesday, November 14, 2017 1:40 PM

Answers

  • Hi Abhishek,

    Try something like this:

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\YOUR_FILE_HERE.txt"), null, null, 1252)}),
        #"Added Conditional Column" = Table.AddColumn(Source, "Commit", each if Text.StartsWith([Column1], "commit ") then [Column1] else null),
        #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Commit"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "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}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",":","",Replacer.ReplaceText,{"Column1.1"}),
        #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1.1] <> "")),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Commit"}, {{"Rows", each Table.RemoveLastN(Table.PromoteHeaders(Table.Transpose(_)), 1), type table}}),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Merge", "Author", "Date"}, {"Merge", "Author", "Date"}),
        #"Trimmed Text" = Table.TransformColumns(#"Expanded Rows",{{"Date", Text.Trim, type text}, {"Author", Text.Trim, type text}, {"Merge", Text.Trim, type text}, {"Commit", Text.Trim, type text}})
    in
        #"Trimmed Text"

    Ehren

    Monday, November 20, 2017 11:02 PM
    Owner