Excel Query manual input issue

MrBJBones

New Member
Joined
Mar 1, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
I'm working on a investment spreadsheet that gathers information from the web. The first 3 columns are gathered from a website, whilst columns D & E are manual inputs & columns F - I formula. Refreshing the query jumbled all the manual inputs around so I tried combining the table with the Query.

1614641943625.png


I followed the instructions from here but to no avail, now whenever I refresh columns D-I are replicated and inserted in between Price and Buy Price:
1614642497697.png


My advanced Editor code is a bit jumbled due to removing the columns all the time:
let
Source = Web.Page(Web.Contents("NZX, New Zealand’s Exchange")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Code", type text}, {"Company", type text}, {"Price", Currency.Type}, {"Change", type text}, {"Volume", Int64.Type}, {"Value", Currency.Type}, {"Capitalisation", Currency.Type}, {"Percentage Change", type number}, {"Type", type text}, {"Green Bond", type logical}, {"Trade Count", Int64.Type}, {"Currency Code", type text}, {"Market Capitalisation", Currency.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Change", "Volume", "Value", "Capitalisation", "Percentage Change", "Type", "Green Bond", "Trade Count", "Currency Code", "Market Capitalisation"}),
Source2 = Excel.CurrentWorkbook(){[Name="InvestmentManager"]}[Content],
DataWithFormula = Table.TransformColumnTypes(Source2,{{"Code", type text}, {"Company", type text}, {"Price", type number}, {"Buy Price", type any}, {"Volume Bought", type any}, {"% Change", type any}, {"Price Paid", type any}, {"Current Value", type any}, {"Profit", type any}}),
#"Merged Queries" = Table.NestedJoin(DataWithFormula, {"Code"}, DataWithFormula, {"Code"}, "DataWithFormula", JoinKind.LeftOuter),
#"Expanded DataWithFormula" = Table.ExpandTableColumn(#"Merged Queries", "DataWithFormula", {"Profit"}, {"DataWithFormula.Profit"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded DataWithFormula",{"Profit"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"DataWithFormula.Profit", "Profit"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Code", "Company", "Price", "Buy Price", "Volume Bought", "% Change", "Price Paid", "Current Value", "Profit"})
in
#"Removed Other Columns"

Does anyone have any suggestions as to how I can keep my formula and manual inputs attached to the companies when I refresh the data?

Any help is greatly appreciated!
Thanks,
Bob
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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
Top