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

[Wolfspyda]

W26040HB-N3(1)-BP1
W26040HB-N3(1)-TP2
W26040HB-N3(1)-ST3
W26040HB-N3(2)-BP1
W26040HB-N3(2)-TP2
W26040HB-N3(2)-ST3
W26036NB-N3(1)-BP1
W26036NB-N3(1)-TP2

<tbody>
</tbody>

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.

W26040HB-N3(1)-BP1
W26040HB-N3(1)-TP2
W26040HB-N3(1)-ST3
W26040HB-N3(2)-BP1
W26040HB-N3(2)-TP2
W26040HB-N3(2)-ST3
W26036NB-N3(1)-BP1
W26036NB-N3(1)-TP2

<tbody>
</tbody>

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.

W26040HB-N3(*)-BP1
W26040HB-N3(*)-BP1
W26040HB-N3(*)-TP2
W26040HB-N3(*)-TP2
W26040HB-N3(*)-ST3
W26040HB-N3(*)-ST3
W26036NB-N3(*)-BP1
W26036NB-N3(*)-TP2

<tbody>
</tbody>

 

[VBA Geek]

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)

[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"?

 

[Wolfspyda]

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.

 

[VBA Geek]

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

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.