Spreadsheet: 829406d1684201984-power-query-custom-column-populating-using-worksheet-names-database
Sheet: ALL

 | ALL STATES
 | State | Customer # | Time & Purchase Date | Name | Gender | DOB | Units | Price per Unit ($) | Total Price ($)
 | TEXAS | 1 | 1701|7 Jun 2011 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-02-19 00:00:00 | 1 | 50 |  | This 'ALL' table is a combination of all of the other worksheet tables. I need help  automatically populating the states from which the information was derived from. Some examples of what I want it to look like are in red. I'm assuming that power query language is the best option with a custom column. I tried to see how to do this in power query but I'm hopeless at it. Thank you for your time, do not hesistate to ask for more context.
 | OHIO | 1 | 1945|25 Aug 2021 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-02-19 00:00:00 | 1 | 50
 | ARIZONA | 1 | 1151|16 Aug 2013 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-02-19 00:00:00 | 1 | 50
 | TEXAS | 2 | 1545|26 Jun 2003 | Jackson Reed | Male | 1999-05-13 00:00:00 | 1900-02-03 00:00:00 | 1.05 | 35.7
 | OHIO | 2 | 2135|27 July 2012 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-19 00:51:25.714000 | 1.05 | 30.08
 | ARIZONA | 2 | 1119|27 Nov 2002 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-19 00:51:25.714000 | 1.05 | 30.08
 | State here | 3 | 2019|27 May 2013 | [NAME] | Male | 1965-05-14 00:00:00 | 1900-02-11 00:00:00 | 1.02 | 42.84
 | State here | 3 | 1318|4 May 2001 | Coren Malibi | Female | 1992-05-19 00:00:00 | 1900-03-18 18:51:25.714000 | 1.02 | 35.839
 | State here | 3 | 1758|18 Nov 2014 | [NAME] | Male | 1992-05-19 00:00:00 | 1900-03-18 18:51:25.714000 | 1.02 | 35.839
 | State here | 4 | 2021|4 Mar 2023 | [NAME] | Male | 2000-05-15 00:00:00 | 1900-01-23 00:00:00 | 1.07 | 24.61
 | State here | 4 | 2336|22 Mar 2003 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-03-18 12:51:25.714000 | 1.07 | 41.598
 | State here | 4 | 1634|8 Oct 2005 | Jackson Reed | Male | 1988-05-12 00:00:00 | 1900-02-15 14:24:00 | 1.137 | 55.591
 | State here | 5 | 1509|16 Apr 2009 | [NAME] | Female | 1989-05-16 00:00:00 | 1900-01-10 00:00:00 | 0.95 | 9.5
 | State here | 5 | 1707|27 Nov 2012 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-18 06:51:25.714000 | 0.95 | 47.357
 | State here | 5 | 2134|8 Oct 2019 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-02-20 09:36:00 | 1.122 | 59.454
 | State here | 6 | 1805|24 Aug 2004 | [NAME] | Male | 1981-05-17 00:00:00 | 1900-04-12 00:00:00 | 0.98 | 100.94
 | State here | 6 | 1923|2 Oct 2017 | [NAME] | Male | 1965-05-14 00:00:00 | 1900-04-12 00:00:00 | 0.98 | 53.116
 | State here | 6 | 1527|20 Sep 2015 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-02-25 04:48:00 | 1.107 | 63.317
 | State here | 7 | 2115|1 Nov 2022 | [NAME] | Female | 1994-05-18 00:00:00 | 1900-03-01 00:00:00 | 1.02 | 62.22
 | State here | 7 | 2152|10 Sep 2016 | [NAME] | Male | 2000-05-15 00:00:00 | 1900-03-01 00:00:00 | 0.951 | 58.875
 | State here | 7 | 1756|14 Sep 2013 | Jackson Reed | Male | 1992-05-19 00:00:00 | 1900-03-01 00:00:00 | 1.092 | 67.18
 | State here | 8 | 1534|18 Oct 2012 | Coren Malibi | Female | 1992-05-19 00:00:00 | 1900-03-06 00:00:00 | 0.9 | 59.4
 | State here | 8 | 1532|12 Oct 2004 | Jackson Reed | Female | 1992-05-19 00:00:00 | 1900-03-06 00:00:00 | 0.93 | 59.4
 | State here | 8 | 1122|18 Jan 2009 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-03-05 19:12:00 | 1.077 | 71.043
 | State here | 9 | 1529|8 Apr 2012 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-03-11 00:00:00 | 1.11 | 78.81
 | State here | 9 | 2256|15 Jan 2014 | [NAME] | Male | 1988-05-12 00:00:00 | 1900-03-11 00:00:00 | 1.11 | 78.81
 | State here | 9 | 1222|13 July 2008 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-11 00:00:00 | 1.11 | 78.81
 | State here | 10 | 2348|16 May 2021 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-15 00:00:00 | 1.01 | 75.75
 | State here | 10 | 1951|11 Feb 2009 | [NAME] | Male | 1999-05-13 00:00:00 | 1900-03-15 00:00:00 | 1.01 | 75.75
 | State here | 10 | 2122|28 Apr 2023 | Jackson Reed | Male | 2000-05-13 00:00:00 | 1900-03-15 00:00:00 | 1.01 | 75.75
 | State here | 11 | 2258|20 Feb 2020 | Jackson Reed | Male | 1965-05-14 00:00:00 | 1900-03-20 00:00:00 | 0.98 | 78.4
 | State here | 11 | 1504|18 Feb 2016 | Jackson Reed | Male | 1965-05-14 00:00:00 | 1900-03-20 00:00:00 | 0.98 | 78.4
 | State here | 11 | 1542|19 July 2000 | [NAME] | Male | 2000-05-14 00:00:00 | 1900-03-20 00:00:00 | 0.98 | 78.4
 | State here | 12 | 1030|7 Apr 2021 | [NAME] | Male | 2000-05-15 00:00:00 | 1900-03-25 00:00:00 | 1.04 | 88.4
 | State here | 12 | 1330|24 Oct 2019 | [NAME] | Male | 2000-05-15 00:00:00 | 1900-03-25 00:00:00 | 1.04 | 88.4
 | State here | 12 | 1603|6 Oct 2008 | [NAME] | Male | 2000-05-15 00:00:00 | 1900-03-25 00:00:00 | 1.04 | 88.4
 | State here | 13 | 1130|22 Jun 2016 | [NAME] | Male | 1989-05-16 00:00:00 | 1900-03-30 00:00:00 | 1.02 | 91.8
 | State here | 13 | 1137|6 Nov 2013 | [NAME] | Male | 1989-05-16 00:00:00 | 1900-03-30 00:00:00 | 1.02 | 91.8
 | State here | 13 | 1213|10 Oct 2019 | [NAME] | Male | 1989-05-16 00:00:00 | 1900-03-30 00:00:00 | 1.02 | 91.8