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 in Others by narikkadan
• 59,740 points
48 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 by Kithuzzz
• 34,760 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
• 2,960 points
83 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,490 points
80 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
• 59,740 points
129 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
• 59,740 points
236 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,151 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
7,580 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,143 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
4,930 views
0 votes
1 answer

How to query a matrix for multiple values and receive value

Try: =IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1) , where MATRIX, LENGTHS, and WEIGHTS ...READ MORE

answered Feb 11 in Others by Kithuzzz
• 34,760 points
85 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