How to arrange data in Power Query

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

June 19th, 2015 4:31pm

It is possible with Power Query. Can you share a sample file, so I will prepare a solution?
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2015 12:40pm

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


  • Edited by Negi1984 Saturday, June 20, 2015 4:48 PM
June 20th, 2015 1:50pm

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

Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 12:08pm

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

June 22nd, 2015 12:05pm

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

Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2015 12:57pm

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

June 23rd, 2015 3:43pm

In the file you shared I don't get these columns. Can you send a sample of the data that creates these columns?
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 4:47pm

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 :-)

June 24th, 2015 12:39pm

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

Free Windows Admin Tool Kit Click here and download it now
June 24th, 2015 2:00pm

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.

June 24th, 2015 7:54pm

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

Free Windows Admin Tool Kit Click here and download it now
June 25th, 2015 11:59am

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

June 25th, 2015 12:20pm

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"

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 3:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics