Power Query 2016 - Table List find special character and replace by count

W

Wolfspyda

New Member
Joined
Mar 28, 2018
Messages
22
[TABLE="width: 500"]
<tbody>[TR]
[TD]W26040HB-N3(1)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(1)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(1)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(1)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(1)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the above Table is the resultant table I want, which is essentially 3 tables merged into 1 after I have used Power Query.

W26040HB-N3 is one table that has a List of Items BP1, TP2 and ST3. W26036NB-N3 only has BP1 and TP2

My table list is as follows with Table W26040HB-N3 repeated twice and W26036NB-N3 once.

I need to make sure the List stays exactly in order as it is in the Source table, but I need it to find the "*" and replace it with how many times the list exists. So in the example above I need it to result as follows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]W26040HB-N3(1)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(1)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(1)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(2)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(1)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(1)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am positive I can do this in Power Query prior to extracting the data into a new table. Currently, the table sees the duplicated items and places them together. see table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]W26040HB-N3(*)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(*)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(*)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(*)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(*)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26040HB-N3(*)-ST3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(*)-BP1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W26036NB-N3(*)-TP2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hello Wolfspyda

in order for us to be of any help we need to understand what the source data looks like, could you confirm it is like this? (Copy paste the code into the advanced editor)

Code: 
[SIZE=2]let[/SIZE]
[SIZE=2]   Source =  {[/SIZE]
[SIZE=2]              #table(type table [Name=Text.Type,Lst=List.Type],[/SIZE]
[SIZE=2]                     {[/SIZE]
[SIZE=2]                      {"W26040HB-N3",[/SIZE]
[SIZE=2]                      {"BP1","TP2","ST3"}}[/SIZE]
[SIZE=2]                     }[/SIZE]
[SIZE=2]                    ),[/SIZE]
[SIZE=2]              [/SIZE]
[SIZE=2]              #table(type table [Name=Text.Type,Lst=List.Type],[/SIZE]
[SIZE=2]                     {[/SIZE]
[SIZE=2]                      {"W26040HB-N3",[/SIZE]
[SIZE=2]                       {"BP1","TP2","ST3"}}[/SIZE]
[SIZE=2]                     }[/SIZE]
[SIZE=2]                   ),[/SIZE]
[SIZE=2]              [/SIZE]
[SIZE=2]             #table(type table [Name=Text.Type,Lst=List.Type],[/SIZE]
[SIZE=2]                    {[/SIZE]
[SIZE=2]                     {"W26036NB-N3",[/SIZE]
[SIZE=2]                      {"BP1","TP2"}}[/SIZE]
[SIZE=2]                    }[/SIZE]
[SIZE=2]                   )[/SIZE]
[SIZE=2]             }[/SIZE]
[SIZE=2]in[/SIZE]
[SIZE=2]    Source[/SIZE]

Then, are you trying to expand the list of each table and then count sequentially the occurrence based on what is under the field "Name"?
 
Upvote 0
Hi VBA Geek,

Not sure why my reply didnt work but is this ok? of is it something else you are looking for?

Source = Csv.Document(File.Contents("Z:\001 ESTIMATING\01 Jobs\003 Steel X\Steelx Shed Panels\N3 Panels.csv"),[Delimiter=",", Columns=125, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", Int64.Type}, {"Column14", type text}, {"Column15", type number}, {"Column16", type text}, {"Column17", type number}, {"Column18", type text}, {"Column19", type number}, {"Column20", type text}, {"Column21", type number}, {"Column22", type text}, {"Column23", type number}, {"Column24", type text}, {"Column25", type number}, {"Column26", type text}, {"Column27", type number}, {"Column28", type text}, {"Column29", type number}, {"Column30", type text}, {"Column31", type number}, {"Column32", type text}, {"Column33", type number}, {"Column34", type text}, {"Column35", type number}, {"Column36", type text}, {"Column37", type number}, {"Column38", type text}, {"Column39", type number}, {"Column40", type text}, {"Column41", type number}, {"Column42", type text}, {"Column43", type number}, {"Column44", type text}, {"Column45", type number}, {"Column46", type text}, {"Column47", type number}, {"Column48", type text}, {"Column49", type number}, {"Column50", type text}, {"Column51", type number}, {"Column52", type text}, {"Column53", type number}, {"Column54", type text}, {"Column55", type number}, {"Column56", type text}, {"Column57", type number}, {"Column58", type text}, {"Column59", type number}, {"Column60", type text}, {"Column61", type number}, {"Column62", type text}, {"Column63", type number}, {"Column64", type text}, {"Column65", type number}, {"Column66", type text}, {"Column67", type number}, {"Column68", type text}, {"Column69", type number}, {"Column70", type text}, {"Column71", type number}, {"Column72", type text}, {"Column73", type number}, {"Column74", type text}, {"Column75", type number}, {"Column76", type text}, {"Column77", type number}, {"Column78", type text}, {"Column79", type number}, {"Column80", type text}, {"Column81", type number}, {"Column82", type text}, {"Column83", type number}, {"Column84", type text}, {"Column85", type number}, {"Column86", type text}, {"Column87", type number}, {"Column88", type text}, {"Column89", type number}, {"Column90", type text}, {"Column91", type number}, {"Column92", type text}, {"Column93", type number}, {"Column94", type text}, {"Column95", type number}, {"Column96", type text}, {"Column97", type number}, {"Column98", type text}, {"Column99", type number}, {"Column100", type text}, {"Column101", type number}, {"Column102", type text}, {"Column103", type number}, {"Column104", type text}, {"Column105", type number}, {"Column106", type text}, {"Column107", type number}, {"Column108", type text}, {"Column109", type number}, {"Column110", type text}, {"Column111", type number}, {"Column112", type text}, {"Column113", type number}, {"Column114", type text}, {"Column115", type number}, {"Column116", type text}, {"Column117", type number}, {"Column118", type text}, {"Column119", type number}, {"Column120", type text}, {"Column121", type number}, {"Column122", type text}, {"Column123", type number}, {"Column124", type text}, {"Column125", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "" and [Column1] <> "DETAILS" and [Column1] <> "VERSION 16.0.0" and [Column1] <> "___________________________________________________________________________________________________________________________________________________")),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Column2", "Column2 - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column2 - Copy", Splitter.SplitTextByDelimiter("(*)", QuoteStyle.Csv), {"Column2 - Copy.1", "Column2 - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2 - Copy.1", type text}, {"Column2 - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column2 - Copy.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2 - Copy.1", "Code List CNC"}})


I tried to screen shot but i cant seem to do that here.
 
Upvote 0
Hello again

I don't think this helps us much. We would need at least a screenshot or to know the content of the CSV file


Wolfspyda said:
Hi VBA Geek,

Not sure why my reply didnt work but is this ok? of is it something else you are looking for?


I tried to screen shot but i cant seem to do that here.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
412
alex78
A
A
  • Question Question
Transforming a Table in Excel Power Query: Grouping and Splitting Rows Based on Unique Order IDs
Replies
3
Views
213
alex78
A
C
  • Question Question
Power Query - finding named tabs/tables in different workbooks in a folder
Replies
1
Views
273
alansidman
alansidman
L
  • Solved
Power Query - filter based on cell outside table
Replies
6
Views
880
Peter_SSs
Peter_SSs
R
  • Solved
Power Query: Merge Query Where Table 1 Has One Field Matching Two Fields in Table 2
Replies
5
Views
705
rasinc
R

Forum statistics

Threads
1,226,258
Messages
6,189,902
Members
453,579
Latest member
MK1212

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top