Populate Many rows the same but change the Customer Code 1

[Cpreston]

Hi

I have a spreadsheet which as rows of customer prices

The columns are made up of Customer Code, Product Code, Length, Price,Date They could be at least 400 rows. However another 30 of our customers may have the same prices.

Example row could be

A1001, Wood123, 100m, £10.00, 01/04/2021

The rows continue in the same format. What I want to be able to do is change the code and copy all the rows for a new customer code automatically in some way. so all the same codes and prices just the customer code will have changed.
The new customer codes could vary in how many depending on prising we are updating.

When the list is complete it is imported into our business system price rules.

Any ideas how to achieve this. Thanks in advance

 

[combo]

It is not clear for me, if all customers have the same set of other data. If so, I would create one table with Customer Code, and the second with other data. Next, with power query (excel 2016+, query from table in 'data' section, for excel data requires structured tables) combine tables and return to new table.

If it is more specific data structure, products datasets can have their ID, duplicated in customers table. The date, depending on the rules, can be either with customers or products. Next processing steps are the same:
1) create queries from input tables, connections only,
2) process the above queries to required output,
3) output the main (2) query to worksheet.

Finally, you update your input tables and refresh output one.

combo

 

[Andrzejek]

Are you saying you have data like this:

[pre]
Customer Code Product Code Length Price Date
X2856 Trasch5 50m 10.00 10/10/2000[blue]
A1001 Wood123 100m 10.00 01/04/2021
A1001 Glass123 200m 15.00 02/04/2021
A1001 Paper123 300m 25.00 03/04/2021[/blue]
XYZ55 Metal34 25m 10.00 05/05/2021
[/pre]
and you want to Copy [blue]BLUE[/blue] records and replace [blue]A1001[/blue] with [green]B2002[/green] in the new rows?

[pre]
Customer Code Product Code Length Price Date
X2856 Trasch5 50m 10.00 10/10/2000[blue]
A1001 Wood123 100m 10.00 01/04/2021
A1001 Glass123 200m 15.00 02/04/2021
A1001 Paper123 300m 25.00 03/04/2021[/blue]
XYZ55 Metal34 25m 10.00 05/05/2021[green]
B2002 Wood123 100m 10.00 01/04/2021
B2002 Glass123 200m 15.00 02/04/2021
B2002 Paper123 300m 25.00 03/04/2021[/green]
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Yes that is correct Andrzejek

Each set would be like that. All customer codes would have the same values listed in the other columns, the only difference would be the Customer Code like you said.
So if customer code as A1001 as 10 lines, so would customer B2002. All customer codes would have the same amount of lines I have a list of some 75 customers and there are 405 lines.

So any ideas how to do this?

Thanks

 

[Andrzejek]

If what you want to do is what I’ve showed you above, and you will do it once in a while, then the process is simple:
[ul]
[li]Select the cells that you want to copy[/li]
[li]Copy the selected cells[/li]
[li]Select first empty cell at the bottom[/li]
[li]Paste[/li]
[li]Select cells with the Customer Code that you want to change[/li]
[li]Find and Replace (Ctrl-H)[/li]
[/ul]
Unless I am missing something here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

See the power query solution in attached file.

combo

 

[Cpreston]

Andrzejek - thanks but copying and pasting would become a huge job, we could have up to 500product lines and maybe a 100 customer, the sheet then becomes quite large

combo - Thanks for the link, I think the spreadsheet could be adapted to use, not quite up on power query but I will give it a go

Many thanks for you reply's

 

[combo]

I think the spreadsheet could be adapted to use, not quite up on power query

But the core of the workbook are power query aka M queries. Since 405/75=5.4, you have not the same product sets for each customer, so IDs. Otherwise no ID required and additional table can be added by adding table to new column, expanding fields and cleaning output.

combo

 

[Cpreston]

The only issue I seem to be having is the currency field

In tproduct it is showing the right prices. But when I refresh touput-from query it is bringing in the Price rounded up to a whole

So where I have £63.75 I have £64.00. I have tried to change the format on the sheet but it keeps it as a whole no matter what I do.

Looked into the query but cannot get it to change so it brings in £63.75 for example.


Any ideas, thanks

 

[Andrzejek]

we could have up to 500product lines and maybe a 100 customer, the sheet then becomes quite large

No matter how you do it (in Excel), your sheet will be ‘quite large’. About (500 x 100) 50 000 rows of data. And when, for example, price of Wood123 changes from 10.00 to 13.00, you would have to change a lot of rows of data.

So, maybe Excel is not the best tool to use…? I see it in a data base with very few, small tables instead.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

To change data type, in query environment, open tProduct query, select Price column

Above, in 'transform' section, change data type to decimal. You will be asked if combine this action with existing step, confirm. As a consequence, the last step 'Change Type' will be modified:

The queries in advanced editor view:
tCustomers
[pre]let
Source = Excel.CurrentWorkbook(){[Name="tCustomers"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Code", type text}, {"ID Product Set", Int64.Type}})
in
#"Changed Type"

tProducts
[pre]let
Source = Excel.CurrentWorkbook(){[Name="tProducts"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Product Set", Int64.Type}, {"Product Code", type text}, {"Length", type text}, {"Price", type number}, {"Data", type date}})
in
#"Changed Type"[/pre][/pre]

tOutput
[pre]let
Source = tCustomers,
#"Merged Queries" = Table.NestedJoin(Source, {"ID Product Set"}, tProducts, {"ID Product Set"}, "tProducts", JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "tProducts", {"ID Product Set", "Product Code", "Length", "Price", "Data"}, {"tProducts.ID Product Set", "tProducts.Product Code", "tProducts.Length", "tProducts.Price", "tProducts.Data"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"ID Product Set", "tProducts.ID Product Set"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"tProducts.Product Code", "Product Code"}, {"tProducts.Length", "Length"}, {"tProducts.Price", "Price"}, {"tProducts.Data", "Data"}})
in
#"Renamed Columns"[/pre]


combo

 

[Cpreston]

Perfect Combo, all working now thanks for the help

Thanks to all for the replys