Power Query - Find matching contents from multiple other tables

0 votes

I am attempting to use Power Query to modify a non-trivial sized amount of data. Several dimensions of data are contained in one column's (let's say, "Column 1") values, but they are not properly demarcated in any way. In this column, I wish to use formulas to do the following things:

  1. with reference to various separate tables (say, "Lookup_n") each listing all possible values for a given dimension, identify whether a substring contained in a table is present in the data in Column1
  2. if it is present, insert that substring into a new column specific to that dimension, and remove it from the data in Column1

Here is an example of what I would like to have to happen:

Sample Output

Feb 16, 2023 in Others by narikkadan
• 63,700 points
328 views

1 answer to this question.

0 votes

Try entering this code in Powerquery's input after constructing the queries lookup 1 (with the lookup 1 column), lookup 2 (with the lookup 2 column), and lookup 3 (with the lookup 3 column).

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lookup = Table.UnpivotOtherColumns( Table.Combine({lookup_3, lookup_2, lookup_1}),{} , "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(Source,"custom",(i)=>(Table.SelectRows(Lookup, each Text.Contains(i[Column_1],[Value])))),
Expanded = Table.ExpandTableColumn(#"Added Custom", "custom", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Column_1", type text}, {"Attribute", type text}, {"Value", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"<none>",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"<none>"})
in #"Removed Columns"

enter image description here

answered Feb 16, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
0 answers

How to drop all tables from a database with one SQL query?

What if we don't want to type ...READ MORE

May 28, 2022 in Others by Sohail
• 3,040 points
216 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

drop all tables without typing name. Is ...READ MORE

Jun 7, 2022 in Others by polo
• 1,480 points
209 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,700 points
481 views
0 votes
1 answer

Stop power query from loading to excel sheet

You can just delete the worksheets created ...READ MORE

answered Dec 17, 2022 in Others by narikkadan
• 63,700 points
4,283 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,961 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,432 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,504 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,794 views
0 votes
1 answer

Combing data from multiple workbooks into a single master workbook using power query

PowerQuery would scan the contents of all ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
331 views
0 votes
1 answer

How to read a dataframe from an excel sheet containing multiple tables?

Here is one option with scikit-image (highly inspired by ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
2,233 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP