locked
How to arrange data in Power Query RRS feed

  • Question

  • Hi All,

    I have a data in .csv or txt file of around more than 30 millions of rows and I am manually arranging data in excel manually the rows count reduced to around 4 million rows.

    I got to know via Power query & Power Pivot I can arrange the huge data in excel.

    Raw data format is like below

    [u0039853@sam ~]$ssh 10.35.9.3

     ---------------------------------------------------------------------------------------------------
    **************************************
    *      Welcome to L1PE-CHA-11        *
    * This device is managed by abcd *
    * Unauthorised access is prohibited  *
    **************************************

    u0039853@10.35.9.3's password:
    Warning: No xauth data; using fake authentication data for X11 forwarding.

    Info: The max number of VTY users is 20, and the number
          of current VTY users on line is 1.
          The current login time is 2015-03-25 13:43:22.
    <L1PE-CHA-11>scre 0 temp
    Info: The configuration takes effect on the current user terminal interface only.
    <L1PE-CHA-11>dis mac-address dynamic verbose
    MAC address table of slot 1:
    -------------------------------------------------------------------------------
    MAC Address: 0819-a6f2-0596     VLAN/VSI/SI   : MOB-MONITOR-983               
    Port       : Tun0/2/15          Type          : dynamic                       
    Peer IP    : 212.224.147.15     VC-ID         : 15                            
    Aging time : 295                LSP/MAC_Tunnel: 1/19458                       

    MAC Address: 2831-52a0-6a4f     VLAN/VSI/SI   : MOB-RAN-1192-HUB              
    Port       : Eth-Trunk10.1192    Type          : dynamic                       
    PEVLAN     : 1                  CEVLAN        : 1192                          
    Peer IP    : -                  VC-ID         : -                             
    Aging time : 295                LSP/MAC_Tunnel: 1/16387     

    and so on..............................................

    Now I need to arrange the above data in below format

    SSH IP : As soon as SSH and IP mentioned than I need the same in first column and need to repeat till next SSH+IP command repeats.

    2) total 10 different heading will be coming from the sample data and if any header is missing than than cell needs to be shown as blank or "-".

    Thanks in advance for your valuable help.

    Regards,

    Rajender

    Friday, June 19, 2015 4:31 PM

Answers

  • Hello Rajener,

    You can copy the following Power Query expression and try it out. It is not 100% of your desired solution, but very close.

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Desktop\MPA Text file.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
    	Text.Contains([Column1], "*      Welcome to") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if ([Column1.1]<> "") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    To understand the rationale behind, follow the steps from the Editor.

    Hope it helps,

    Gil

    • Proposed as answer by Gil RavivMVP Sunday, June 21, 2015 12:08 PM
    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 3:12 PM
    Sunday, June 21, 2015 12:08 PM
  • Hi Rajender,

    I am glad that this solution worked for you. Please mark it as an answer, so others will be able to take benefit of it next time they want to parse SSH logs :)

    You can use this page to start learning more on Power Query. There are many resources and expert blogs that can also take you to the next levels -

    Here are few that come top of my mind -

    http://blog.crossjoin.co.uk/

    http://www.excelguru.ca/blog/

    http://www.powerpivotpro.com/

    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 3:11 PM
    Wednesday, July 1, 2015 2:10 PM
  • Hi Rajender

    The the correct formula is below. In the previous formula I used blank lines to increment an index and use it to create a key for the pivot. In the input file there were cases whitespaces were used to separate between multiple records, and these whitespaces were ignored. As a result, the pivot operation failed in rows that were not separated by blank lines.

    To fix it, I use lines with "MAC Address" to signal new records. We assume here that new records will always starts with the MAC Address as the first line of data per record.

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Desktop\MPA Text file.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if not Text.StartsWith([Column1.1],"MAC Address") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    Hope it helps,

    Gil

    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 1:36 PM
    Monday, June 22, 2015 12:57 PM
  • Hi Rajender,

    With the new text file you have sent you can see that there are records that starts with:

    "  ---- More ----                                          " before the MAC Address. These lines cause the errors.

    Since we assumed that lines that starts with "MAC Address" are used to mark new records, in cases where the prefix exists, we miss the "MAC Address" that signals a new record, and as a result during the pivot step we find several lines with the same keys (e.g. multiple lines with Port values). Since the pivot operation doesn't expect to get a list, you get these errors. 

    To fix the problem, you should add a third step that will remove the problematic prefix:

    = Table.ReplaceValue(#"Filtered Rows","  ---- More ----#(001B)[42D                                          #(001B)[42D","",Replacer.ReplaceText,{"Column1"})

    Here is the entire Power Query expression:

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Downloads\MPA DYNAMIC-CONSOLIDATE\MPA DYNAMIC-CONSOLIDATE.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","  ---- More ----#(001B)[42D                                          #(001B)[42D","",Replacer.ReplaceText,{"Column1"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if not Text.StartsWith([Column1.1],"MAC Address") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    • Proposed as answer by Gil RavivMVP Monday, June 29, 2015 7:19 AM
    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 1:36 PM
    Monday, June 29, 2015 7:19 AM

All replies

  • It is possible with Power Query. Can you share a sample file, so I will prepare a solution?
    Saturday, June 20, 2015 12:40 PM
  • Hi Gil,

    I have uploaded the raw data file and final output final for 3 IP's only.

    I also mentioned in heading which columns are related to txt file and which are not for easy reference.

    https://onedrive.live.com/redir?resid=393CD98CEE2A349F!133&authkey=!AHwGZi5g0Xemg-Q&ithint=folder%2c

    Hope I clear to explain my query.

    Regards,

    Rajender


    Saturday, June 20, 2015 1:50 PM
  • Hello Rajener,

    You can copy the following Power Query expression and try it out. It is not 100% of your desired solution, but very close.

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Desktop\MPA Text file.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
    	Text.Contains([Column1], "*      Welcome to") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if ([Column1.1]<> "") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    To understand the rationale behind, follow the steps from the Editor.

    Hope it helps,

    Gil

    • Proposed as answer by Gil RavivMVP Sunday, June 21, 2015 12:08 PM
    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 3:12 PM
    Sunday, June 21, 2015 12:08 PM
  • Hi Gil,

    Thanks a ton, it will really save my lots of time.

    I randomly checked the detail for IP 10.35.9.15, there is a difference of 9 rows data which is missing in final output by using above code.

    Total 277 lines showing error and when I clicked in error than it opened a query window and the missing lines showing there and with error message in cell.

    Could you please assist how to tackle with error and missing data ?

    IP 10.35.9.15 missing data showing under error head.

    Total error 277

    Please assist.

    Rajender

    Monday, June 22, 2015 12:05 PM
  • Hi Rajender

    The the correct formula is below. In the previous formula I used blank lines to increment an index and use it to create a key for the pivot. In the input file there were cases whitespaces were used to separate between multiple records, and these whitespaces were ignored. As a result, the pivot operation failed in rows that were not separated by blank lines.

    To fix it, I use lines with "MAC Address" to signal new records. We assume here that new records will always starts with the MAC Address as the first line of data per record.

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Desktop\MPA Text file.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if not Text.StartsWith([Column1.1],"MAC Address") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    Hope it helps,

    Gil

    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 1:36 PM
    Monday, June 22, 2015 12:57 PM
  • Hi Gil,

    The new line always started with mac address:  instead of only mac address

    I test the new code in current report it shows all the data without error. will get back to you after cross check manual too if the data mapped correctly( around 422000 rows).

    I also cross checked the same with my last extract report where I found error around 39000 rows.

    and found this column extra showing in the report.

    I think we need to add one more criteria for the same ?

    Regards,

    Rajender

    Tuesday, June 23, 2015 3:43 PM
  • In the file you shared I don't get these columns. Can you send a sample of the data that creates these columns?
    Tuesday, June 23, 2015 4:47 PM
  • Hi Negi :-)

    Here is another one code for your text file...  (not better, just different than Gil's code).
    I think, there are all information you need.

    let
        Source      = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\Zbigniew\Downloads\Z pendrive\MPA Text file.txt"),null,null,1252)}),
        AddColumn1  = Table.AddColumn(Source, "PE IP", each if Text.Contains([Column1], "]$ssh ") then [Column1] else null),
        SplitCol1   = Table.SplitColumn(AddColumn1,"PE IP",Splitter.SplitTextByEachDelimiter({" "}, null, true),{"Custom.1", "PE IP"}),
        ChType1     = Table.TransformColumnTypes(SplitCol1,{{"Custom.1", type text}, {"PE IP", type text}}),
        RemoveCol1  = Table.RemoveColumns(ChType1,{"Custom.1"}),
        AddColumn2  = Table.AddColumn(RemoveCol1, "PE", each if Text.Contains([Column1],"dis mac-address dynamic verbose") then Text.Start([Column1],Text.PositionOf([Column1],">")) else null),
        ChType2     = Table.TransformColumnTypes(AddColumn2,{{"PE", type text}}),
        PeplaceVal1 = Table.ReplaceValue(ChType2,"<","",Replacer.ReplaceText,{"PE"}),
        AddColumn3  = Table.AddColumn(PeplaceVal1, "Slot", each if Text.StartsWith([Column1],"MAC address table of slot ") then Text.Replace([Column1], "MAC address table of slot ","") else null),
        ChType3     = Table.TransformColumnTypes(AddColumn3,{{"Slot", type text}}),
        ReplaceVal2 = Table.ReplaceValue(ChType3,":","",Replacer.ReplaceText,{"Slot"}),
        FillDown1   = Table.FillDown(ReplaceVal2,{"PE IP", "PE", "Slot"}),
        AddCol4     = Table.AddColumn(FillDown1, "Custom", each if Text.StartsWith([Column1],"MAC Address:") or Text.StartsWith([Column1],"Port       :") or Text.StartsWith([Column1],"PEVLAN     :") or Text.StartsWith([Column1],"TrustFlag  :") or Text.StartsWith([Column1],"Peer IP    :") or Text.StartsWith([Column1],"Aging time :") or Text.StartsWith([Column1],"TimeStamp  :") or [Column1]="" then 1 else null),
        FilterRows1 = Table.SelectRows(AddCol4, each ([Custom] = 1)),
        SplitCol2   = Table.SplitColumn(FilterRows1,"Column1",Splitter.SplitTextByDelimiter(":"),{"Column1.1", "Column1.2", "Column1.3"}),
        ChType4     = Table.TransformColumnTypes(SplitCol2,{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
        TrimText1   = Table.TransformColumns(ChType4,{{"Column1.1", Text.Trim}, {"Column1.2", Text.Trim}, {"Column1.3", Text.Trim}}),
        SplitCol3   = Table.SplitColumn(TrimText1,"Column1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.2.1", "Column1.2.2"}),
        ChType5     = Table.TransformColumnTypes(SplitCol3,{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
        TrimText2   = Table.TransformColumns(ChType5,{{"Column1.2.1", Text.Trim}, {"Column1.2.2", Text.Trim}}),
        ReplaceVal3 = Table.ReplaceValue(TrimText2,null,"ToRemove",Replacer.ReplaceValue,{"Column1.2.2", "Column1.3"}),
        ListColNam  = List.Buffer({"MAC Address","VLAN/VSI/SI","Port","Type","PEVLAN","CEVLAN","TrustFlag","TrustPort","Peer IP","VC-ID","Aging time","LSP/MAC_Tunnel","TimeStamp"}),
        ListColVal  = List.Buffer( List.Repeat({"---"},13)),
        AddCol5     = Table.AddColumn(ReplaceVal3, "Rec", each if [Column1.1] = "" then Record.FromList(ListColVal,ListColNam) else Record.FromList({[Column1.2.1],[Column1.3]},{[Column1.1],[Column1.2.2]})),
        RemCol2     = Table.RemoveColumns(AddCol5,{"Column1.1", "Column1.2.1", "Column1.2.2", "Column1.3", "Custom"}),
        ExpRec      = Table.ExpandRecordColumn(RemCol2, "Rec", {"MAC Address", "VLAN/VSI/SI", "Port", "Type", "PEVLAN", "CEVLAN", "TrustFlag", "TrustPort", "Peer IP", "VC-ID", "Aging time", "LSP/MAC_Tunnel", "TimeStamp", "ToRemove"}, {"MAC Address", "VLAN/VSI/SI", "Port", "Type", "PEVLAN", "CEVLAN", "TrustFlag", "TrustPort", "Peer IP", "VC-ID", "Aging time", "LSP/MAC_Tunnel", "TimeStamp", "ToRemove"}),
        AddCol6     = Table.AddColumn(ExpRec, "Mod", each if [MAC Address]<>null and [MAC Address] <> "---" then 1 else null),
        FillUp1     = Table.FillUp(AddCol6,{"MAC Address", "VLAN/VSI/SI", "Port", "Type", "PEVLAN", "CEVLAN", "TrustFlag", "TrustPort", "Peer IP", "VC-ID", "Aging time", "LSP/MAC_Tunnel", "TimeStamp", "ToRemove"}),
        FilterRows2 = Table.SelectRows(FillUp1, each ([Mod] = 1)),
        RemCol3     = Table.RemoveColumns(FilterRows2,{"ToRemove", "Mod"}),
        ChType6     = Table.TransformColumnTypes(RemCol3,{{"TimeStamp", type text}, {"LSP/MAC_Tunnel", type text}, {"Aging time", type text}, {"VC-ID", type text}, {"Peer IP", type text}, {"TrustPort", type text}, {"TrustFlag", type text}, {"CEVLAN", type text}, {"PEVLAN", type text}, {"Type", type text}, {"Port", type text}, {"VLAN/VSI/SI", type text}, {"MAC Address", type text}, {"Slot", type text}, {"PE", type text}, {"PE IP", type text}}),
        ReplaceVal4 = Table.ReplaceValue(ChType6,"---","",Replacer.ReplaceText,{"TrustFlag", "TrustPort", "Peer IP", "VC-ID", "TimeStamp"})
    in
        ReplaceVal4

    Regards :-)

    Wednesday, June 24, 2015 12:39 PM
  • Hi Gil,

    I have uploaded my last complete report final output and raw data both in below link

    https://onedrive.live.com/redir?resid=393CD98CEE2A349F!136&authkey=!APRz02PgtJNSmFI&ithint=folder%2c

    Thanks in advance.

    Regards,

    Rajender

    Wednesday, June 24, 2015 2:00 PM
  • Hi Rajender,

    I tried the query with the new file that you sent. All works well. I didn't get the wrong columns you have shown above.

    Wednesday, June 24, 2015 7:54 PM
  • Hi Gil,

    Sorry I uploaded wrong file.

    Below is the updated one.

    https://onedrive.live.com/redir?resid=393CD98CEE2A349F!137&authkey=!AIgjnN8hC2q6rbs&ithint=file%2czip

    Note : I current raw data its working fine, but when I am running the same in attached raw file than its showing around 28000 errors.

    Regards,

    Rajender

    Thursday, June 25, 2015 11:59 AM
  • Hi Bill,

    I run the above code 3 times , but every time its showing out of memory message.

    Unexpected error: Out of Memory

    Regards,

    Rajender

    Thursday, June 25, 2015 12:20 PM
  • Hi Rajender,

    With the new text file you have sent you can see that there are records that starts with:

    "  ---- More ----                                          " before the MAC Address. These lines cause the errors.

    Since we assumed that lines that starts with "MAC Address" are used to mark new records, in cases where the prefix exists, we miss the "MAC Address" that signals a new record, and as a result during the pivot step we find several lines with the same keys (e.g. multiple lines with Port values). Since the pivot operation doesn't expect to get a list, you get these errors. 

    To fix the problem, you should add a third step that will remove the problematic prefix:

    = Table.ReplaceValue(#"Filtered Rows","  ---- More ----#(001B)[42D                                          #(001B)[42D","",Replacer.ReplaceText,{"Column1"})

    Here is the entire Power Query expression:

    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gilraviv\Downloads\MPA DYNAMIC-CONSOLIDATE\MPA DYNAMIC-CONSOLIDATE.txt"),null,null,1252)}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "]$ssh") or
            Text.Length([Column1]) = 0 or
            Text.Contains([Column1], "MAC Address:") or
            Text.Contains([Column1], "Port       :") or 
            Text.Contains([Column1], "PEVLAN     :") or 
            Text.Contains([Column1], "TrustFlag  :") or 
            Text.Contains([Column1], "Peer IP    :") or 
            Text.Contains([Column1], "Aging time :") or 
            Text.Contains([Column1], "TimeStamp  :") or 
            Text.Contains([Column1], "VLAN/VSI/SI   :") or 
            Text.Contains([Column1], "Type          :") or 
            Text.Contains([Column1], "CEVLAN        :") or 
            Text.Contains([Column1], "TrustPort     :") or 
            Text.Contains([Column1], "VC-ID         :") or 
            Text.Contains([Column1], "LSP/MAC_Tunnel:")),
        #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","  ---- More ----#(001B)[42D                                          #(001B)[42D","",Replacer.ReplaceText,{"Column1"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value","Column1",Splitter.SplitTextByEachDelimiter({"]$ssh "}, null, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if not Text.StartsWith([Column1.1],"MAC Address") then null else [Index]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2", "Custom"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Down","Column1.1",Splitter.SplitTextByDelimiter(":"),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
        #"Trimmed Text2" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Column1.1.1", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.1.3", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1.2", Text.Trim}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text","Column1.1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.1.2.1", "Column1.1.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.2.1", type text}, {"Column1.1.2.2", type text}}),
        #"Trimmed Text1" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2.2", Text.Trim}, {"Column1.1.3", Text.Trim}}),
        #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Column1.1.1", "Column1.1.2.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
        #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1.2.2", "Column1.1.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Column1.2", "Custom"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Value",Splitter.SplitTextByEachDelimiter({":"}, null, false),{"Value.1", "Value.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.2", "IP"}, {"Value.1", "Field"}, {"Value.2", "Value"}, {"Custom", "Key"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "" and [Value] <> "-")),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Field]), "Field", "Value")
    in
        #"Pivoted Column"

    • Proposed as answer by Gil RavivMVP Monday, June 29, 2015 7:19 AM
    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 1:36 PM
    Monday, June 29, 2015 7:19 AM
  • Hi Gil,

    Thanks a lot for your valuable support. it saved my lots of time.

    I cross checked the above codes thoroughly and run it in all the previous reports and found that there is non consistency in raw data while extracting for example in above case new column inserted due to " ---- More ----                                          " or there are other same thing I noticed.

    For the same what I did whenever there is a new column reflects by running this code. I go to raw file and replace all those fields with "blank". This working fine too :-)

    I have similar 5 reports almost few difference will do it by myself.

    Also I want to learn about this , Could you please suggest how and where to learn about this ?

    once again many thanks for your support

    Regards,

    Rajender

    Wednesday, July 1, 2015 1:46 PM
  • Hi Rajender,

    I am glad that this solution worked for you. Please mark it as an answer, so others will be able to take benefit of it next time they want to parse SSH logs :)

    You can use this page to start learning more on Power Query. There are many resources and expert blogs that can also take you to the next levels -

    Here are few that come top of my mind -

    http://blog.crossjoin.co.uk/

    http://www.excelguru.ca/blog/

    http://www.powerpivotpro.com/

    • Marked as answer by AskQuery1984 Wednesday, July 1, 2015 3:11 PM
    Wednesday, July 1, 2015 2:10 PM
  • Hi Gil,

    Thanks a lot once again.

    Regards,

    Rajender

    Wednesday, July 1, 2015 3:15 PM
  • Hi Rajender,

    I am working on a blog post to show how to use Excel and Power Query to clean and analyze raw security logs.

    Can I share your logs above as part of the blog post? Would you like to share information about how you utilize Excel to gain insights? Let me know if you would like to mention your name as part of the blog, and feel free to contact me on twitter: https://twitter.com/gilra

    Saturday, July 11, 2015 10:13 AM
  • Hi Gil,

    Sorry for late revert , I just saw your message. You can share the above post. just want to say company info not needs to be share for which it is related to rest things are ok for me :-).

    And really you save my lots of time. I also purchase Power Query books for learning and referring sites mentioned by you.

    Have a great day to you.

    Please let me know if you need any further details from me.

    Regards,

    Rajender

    Tuesday, July 14, 2015 4:18 PM
  • Thank you Rajender
    Can you share your role (e.g Security Analyst) and what was your analysis goal (e.g detecting suspicious connections to assets)?
    Tuesday, July 14, 2015 4:29 PM
  • Hi Gil,

    I just entered into Analytical field. Currently my work My Role is like Data Analyst.

    Currently I need to extract reports from tools using SSH and Telnet Command.

    The Data which I am extracting consisting around 300MB in size in log files.

    After that I need to Arrange the data in Tabular format 1 of the above query is example.

    After that I need to analysis based on raw data that how many sites are related to 2G/3G/4G , running on which technology, location, all about network etc.....

    And after that 1 final report I am preparing with multiple conditions(Looking for the automated solution with post query in this forum later on).

    Need to go now. Please let me know if any further assistance I can do ?

    Regards,

    Rajender

    Skype ID: Negi1984

     

    Tuesday, July 14, 2015 4:44 PM
  • Thank you :)
    Wednesday, July 15, 2015 4:08 PM
  • You are Welcome :-)

    Please also let me know once your Blog completed for the same :-).

    Regards,

    Rajender

    Monday, July 20, 2015 3:40 PM
  • For sure :)

    You can follow it on my blog here.

    My first post on Excel for Security Analysts was about different log files, but yours is the next.

    Monday, July 20, 2015 3:44 PM
  • The blog post is ready. Thank you again :) http://blogs.technet.com/b/gilraviv/archive/2015/08/04/excel-for-security-analysts-episode-2.aspx
    Tuesday, August 4, 2015 2:02 PM
  • Hi Gil,

    I saw it is very useful to others too ,thanks a lot you too :-).

    I want to know one more thing from you, if I need to import data from SAP also for selected data in excel like Network codes and few more columns and I need to extract the data for the same from SAP.

    If anything like this we can do from Power Query ? 

    Regards,

    Rajender

    Friday, August 7, 2015 2:06 PM
  • Hi Rajender, Could you submit this question in a new thread? I am afraid that it will not reach enough exposure here. Not sure I understand the question. You may want to elaborate more on the new thread :) Thank you, Gil
    Friday, August 7, 2015 3:02 PM
  • Hi Gil,

    Hope you doing well.

    I need your help once more time. I above solution you have provided to me , now I am getting Out of memory error". the calculation showing in status bar goes upto 552 MB and then this error come.

    I am currently using Window 7 , 64 bit and office 2010 professional Plus 32 bit.

    Could you please suggest any workaround.

    Thanks in advance for your valuable suggestion.

    Regards,

    Rajender

    Thursday, January 28, 2016 10:23 AM
  • Upgrade to 64-bit office and also potentially get a machine with more RAM.

    In general it's better to start a new thread than to resurrect an old one. You can always include a link to the original thread if the context is relevant.

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    Thursday, January 28, 2016 2:43 PM
  • Hi Greg,

    I will take care of this and ask question in a new thread.

    Also I already extended my RAM to 16 GB.

    Regards,

    Rajender

    Thursday, January 28, 2016 3:57 PM
  • Then your limit is definitely the 32-bit installation of Excel.

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    Thursday, January 28, 2016 5:05 PM
  • Hi Greg,

    I installed office 2013 64 bit professional plus , but again same error :-(.

    If you can suggest any workaround.

    Regards,

    Rajender

    Tuesday, February 2, 2016 12:04 PM