one Column with 6000 rows convert into 6 columns into 1000 rows based on conditions

[waqar1239]

Hello Team
Here is the example
[TABLE="width: 251"]
<tbody>[TR]
[TD="width: 335, bgcolor: transparent"]# 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 CREATE POINT 744STEAM:FA0032.PNT
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DESC = "4 RECOVERY MCR"
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AUDITUPD = NO
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COLLECTOR = srhc03
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]# 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 CREATE POINT 735WLCLF01:FY0046B.RO01
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DESC = "WASH FACTOR NO2 MUD WASHER"
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AUDITUPD = NO
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]# 3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 CREATE POINT C201CP_STA:STATION.MINUTE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DESC =
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AUDITUPD = NO
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COLLECTOR = srhc03
[/TD]
[/TR]
</tbody>[/TABLE]

Output

[TABLE="width: 784"]
<tbody>[TR]
[TD="width: 24, bgcolor: transparent"]# 1
[/TD]
[TD="width: 253, bgcolor: transparent"]1 CREATE POINT 744STEAM:FA0032.PNT
[/TD]
[TD="width: 307, bgcolor: transparent"]1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT
[/TD]
[TD="width: 199, bgcolor: transparent"]DESC = "4 RECOVERY MCR"
[/TD]
[TD="width: 118, bgcolor: transparent"]AUDITUPD = NO
[/TD]
[TD="width: 142, bgcolor: transparent"]COLLECTOR = srhc03
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]# 2
[/TD]
[TD="bgcolor: transparent"]1 CREATE POINT 735WLCLF01:FY0046B.RO01
[/TD]
[TD="bgcolor: transparent"]1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT
[/TD]
[TD="bgcolor: transparent"]DESC = "WASH FACTOR NO2 MUD WASHER"
[/TD]
[TD="bgcolor: transparent"]AUDITUPD = NO
[/TD]
[TD="bgcolor: transparent"]COLLECTOR = srhc03
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]# 3
[/TD]
[TD="bgcolor: transparent"]1 CREATE POINT C201CP_STA:STATION.MINUTE
[/TD]
[TD="bgcolor: transparent"]1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER
[/TD]
[TD="bgcolor: transparent"]DESC =
[/TD]
[TD="bgcolor: transparent"]AUDITUPD = NO
[/TD]
[TD="bgcolor: transparent"]COLLECTOR = srhc03
[/TD]
[/TR]
</tbody>[/TABLE]


I have this type of data. Please advise me how i can do it.
Rgards
Heaps of thanks in advanc

 

[Misca]

Sounds like something you should do with Power Query (or "Get and Transform" as it's currently called):

You can find Power Query from the Data tab of your Excel ribbon. Use the "Get Data" button and select the right kind of data source. I'd recommend you don't import the data into Excel manually but point Power Query to the original file / data base if possible. This step might take a couple of extra clicks before you have your data visible in your Power Query editor. Also, make sure your data type is set to Text ( ie. you can see an ABC icon in the column header. If not click the icon and change it to text.).

The first actual step you want to do after you've got your data set in text is to turn it into upper case. Do this using the Format-button on the Transform tab.
Next go to the Add Column -tab and create a Custom Column. Call it what you want. It is only going to contain the case / instance number.

I'd use something like

= if Text.Start([Data],1) ="#" then [Data] else null

as the Custom column formula. Make sure you'll use whatever is the right column name in your data instead of "[Data]". Insert the right column name using the Available columns list.

Next click on the column header of your new column with your right mouse button and select Fill / Down. All the nulls will be replaced with the case / instance number.

Click on your original data column and set the Text Filter to Does Not Begin with #. Also if you have blank rows in your data column filter those out as well.

Once you're left with nothing but the data rows in your data column create another custom column. This time use something like

=Text.Start([Data],4)

as the custom column formula. This takes the first 4 characters from your Data column. You're going to use these to create new columns in your next step.

Select the new column and press the Pivot Column button found in the Transform tab. Select your data column as the Values Column and click on the Advanced options and select the Don't Aggregate option from the drop down. This is essential. Also, if some of your instances have more than a single row of each column name you're going to end up with errors after this step.

Now you're almost done. Just rename your columns to what you want and click on the Close & Load button on your Home tab. Also, you might want to rename your query by typing a new name on the Name box on the Query Settings pane you should see on your right at the Query editor window. The query name will also be used as the table name of your final table.

This step might take a bit longer, depending on the number of rows etc. but you're going to end up with just what you wanted.

The beauty of using Power Query for this is that the next time you're going to need to update your data all you need to do is open the Excel file and press the "Update All" button on your Data tab. Power Query will remember all the steps you've made and you can update your data over and over again with a simple click of a button.

I tried to list all the steps needed but in case I missed something you might want to watch some of the Power Query tutorials found on YouTube. Here's a great one by ExcelIsFun. It's 50 minutes long but well worth your time.

 

[sandy666]

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1.1[/td][td=bgcolor:#70AD47]Custom.1.2[/td][td=bgcolor:#70AD47]Custom.1.3[/td][td=bgcolor:#70AD47]Custom.1.4[/td][td=bgcolor:#70AD47]Custom.1.5[/td][td=bgcolor:#70AD47]Custom.1.6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]# 1[/td][td][/td][td=bgcolor:#E2EFDA]# 1[/td][td=bgcolor:#E2EFDA]1 CREATE POINT 744STEAM:FA0032.PNT[/td][td=bgcolor:#E2EFDA]1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT[/td][td=bgcolor:#E2EFDA]DESC = 4 RECOVERY MCR[/td][td=bgcolor:#E2EFDA]AUDITUPD = NO[/td][td=bgcolor:#E2EFDA]COLLECTOR = srhc03[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1 CREATE POINT 744STEAM:FA0032.PNT[/td][td][/td][td]# 2[/td][td]1 CREATE POINT 735WLCLF01:FY0046B.RO01[/td][td]1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT[/td][td]DESC = WASH FACTOR NO2 MUD WASHER[/td][td]AUDITUPD = NO[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT[/td][td][/td][td=bgcolor:#E2EFDA]# 3[/td][td=bgcolor:#E2EFDA]1 CREATE POINT C201CP_STA:STATION.MINUTE[/td][td=bgcolor:#E2EFDA]1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER[/td][td=bgcolor:#E2EFDA]DESC =[/td][td=bgcolor:#E2EFDA]AUDITUPD = NO[/td][td=bgcolor:#E2EFDA]COLLECTOR = srhc03[/td][/tr]

[tr=bgcolor:#FFFFFF][td]DESC = "4 RECOVERY MCR"[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AUDITUPD = NO[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]COLLECTOR = srhc03[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]# 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1 CREATE POINT 735WLCLF01:FY0046B.RO01[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]DESC = "WASH FACTOR NO2 MUD WASHER"[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AUDITUPD = NO[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]# 3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 CREATE POINT C201CP_STA:STATION.MINUTE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DESC =[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]AUDITUPD = NO[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]COLLECTOR = srhc03[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table7
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "#") then [Column1] else null),
    FillD = Table.FillDown(IF,{"Custom"}),
    Group = Table.Group(FillD, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom.1", each Table.Column([Count],"Column1")),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "@"), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([Custom.1],"@"))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Extract, "Custom.1", Splitter.SplitTextByAnyDelimiter({"@"}, QuoteStyle.Csv)),
    RC = Table.RemoveColumns(Split,{"Custom", "Count"})
in
    RC[/SIZE]