Get transformed table from 2 input tables PQ

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
310
Office Version
  1. 2019
Platform
  1. Windows
Hello. May somebody help me with how to do this in Power Query. I'm using Excel 2016.

I have the following 2 input tables.



TABLE 1TABLE 2
DESCRIPTION VALUE
PREFIX CODE
ID07235 ABX1
NAME JFMSC3553 POWQ
TYPE UHELQ7459 UWEER
DFRUL F410012 ABX1
ADDR10012002430 ABX1
RRUL P1
ADDR723
RRUL P1
ID2
NAME PLLSJS
TYPE UHELQ
DFRUL P3
ID4
NAME AAAARR
TYPE UHELQ
DFRUL T7
ADDR35531156
RRUL P1
ADDR72358
RRUL P1
ADDR86401
RRUL K9
ID0
NAME PPROOA
TYPE RRHN
DFRUL P1
ADDR43001
RRUL T8
ADDR7459001
RRUL D4
ADDR430457
RRUL W2
ADDR745913
RRUL P1
ADDR74598001
RRUL Y5

<tbody>
</tbody>





I want to Transform the data from Table1 like below.


  • * The last field in output (CODE) results from compare criterias based on Table2. This is match the ADDR value with the most similar PREFIX of Table2 and show the related CODE.
  • * For the case of the value 86401 in fieldADDR, since there is no related PREFIX in Table2 for this value, then CODE field shows NOT FOUND.



IDNAMETYPEDFRULADDRRRULCODE
0JFMSCUHELQF410012002P1ABX1
0JFMSCUHELQF4723P1ABX1
2PLLSJSUHELQP3
4AAAARRUHELQT735531156P1POWQ
4AAAARRUHELQT772358P1ABX1
4AAAARRUHELQT786401K9NF
0PPROOARRHNP143001T8ABX1
0PPROOARRHNP17459001D4UWEER
0PPROOARRHNP1430457W2ABX1
0PPROOARRHNP1745913P1UWEER
0PPROOARRHNP174598001Y5UWEER

<tbody>
</tbody>




Many thanks in advance for any help.
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]// Source[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source =Web.Page(Web.Contents("https://www.mrexcel.com/forum/power-bi/1091265-get-transformed-table-2-input-tables-pq.html")),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Data0 =Source{0}[Data],[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType" = Table.TransformColumnTypes(Data0,{{"Column1", typetext}, {"Column2", type text}, {"Column3", type text},{"Column4", type text}, {"Column5", type text}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"Removed TopRows" = Table.Skip(#"Changed Type",1),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"PromotedHeaders" = Table.PromoteHeaders(#"Removed Top Rows",[PromoteAllScalars=true]),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType1" = Table.TransformColumnTypes(#"PromotedHeaders",{{"DESCRIPTION", type text}, {"VALUE", typetext}, {"", type text}, {"PREFIX", Int64.Type},{"CODE", type text}})[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType1"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// Table1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source = Source,[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RemovedOther Columns" = Table.SelectColumns(Source,{"DESCRIPTION","VALUE"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"AddedIndex" = Table.AddIndexColumn(#"Removed Other Columns","Index", 1, 1),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType" = Table.TransformColumnTypes(#"AddedIndex",{{"Index", type text}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"AddedConditional Column" = Table.AddColumn(#"Changed Type","Custom", each if [DESCRIPTION] = "ID" then [VALUE] &":" & [Index] else null),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RemovedColumns" = Table.RemoveColumns(#"Added ConditionalColumn",{"Index"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"FilledDown" = Table.FillDown(#"Removed Columns",{"Custom"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"FilteredRows" = Table.SelectRows(#"Filled Down", each ([DESCRIPTION]<> "ID"))[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"FilteredRows"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// List[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source = Table1,[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"FilteredRows" = Table.SelectRows(Source, each ([DESCRIPTION] = parDesc) and([Custom] = parID)),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RemovedColumns" = Table.RemoveColumns(#"FilteredRows",{"DESCRIPTION", "Custom"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RenamedColumns" = Table.RenameColumns(#"Removed Columns",{{"VALUE",parDesc}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"DemotedHeaders" = Table.DemoteHeaders(#"Renamed Columns"),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType" = Table.TransformColumnTypes(#"DemotedHeaders",{{"Column1", type text}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Column1 =#"Changed Type"[Column1][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Column1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// parDesc[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]"ADDR" meta [IsParameterQuery=true,Type="Text", IsParameterQueryRequired=true][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// parID[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]"0:1" meta [IsParameterQuery=true,Type="Text", IsParameterQueryRequired=true][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// fnGetList[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source = (parDescas text, parID as text) => let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Source =Table1,[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       #"Filtered Rows" = Table.SelectRows(Source, each([DESCRIPTION] = parDesc) and ([Custom] = parID)),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"RemovedColumns" = Table.RemoveColumns(#"FilteredRows",{"DESCRIPTION", "Custom"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"RenamedColumns" = Table.RenameColumns(#"RemovedColumns",{{"VALUE", parDesc}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"DemotedHeaders" = Table.DemoteHeaders(#"Renamed Columns"),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"ChangedType" = Table.TransformColumnTypes(#"DemotedHeaders",{{"Column1", type text}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Column1 =#"Changed Type"[Column1][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Column1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// Repeats[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source =Table.FromColumns({fnGetList("ADDR",parID),fnGetList("RRUL",parID)}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"PromotedHeaders" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType" = Table.TransformColumnTypes(#"PromotedHeaders",{{"ADDR", Int64.Type}, {"RRUL", type text}})[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// fnRepeats[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source = (parID astext) => let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Source =Table.FromColumns({fnGetList("ADDR",parID),fnGetList("RRUL",parID)}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       #"Promoted Headers" = Table.PromoteHeaders(Source,[PromoteAllScalars=true]),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"ChangedType" = Table.TransformColumnTypes(#"PromotedHeaders",{{"ADDR", Int64.Type}, {"RRUL", type text}})[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        #"ChangedType"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]// Output[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]let[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Source = Table1,[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"FilteredRows" = Table.SelectRows(Source, each ([DESCRIPTION] <>"ADDR" and [DESCRIPTION] <> "RRUL")),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"PivotedColumn" = Table.Pivot(#"Filtered Rows",List.Distinct(#"Filtered Rows"[DESCRIPTION]),"DESCRIPTION", "VALUE"),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"InvokedCustom Function" = Table.AddColumn(#"Pivoted Column","Custom.1", each fnRepeats([Custom])),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ExpandedCustom.1" = Table.ExpandTableColumn(#"Invoked Custom Function","Custom.1", {"ADDR", "RRUL"}, {"ADDR","RRUL"}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ChangedType" = Table.TransformColumnTypes(#"ExpandedCustom.1",{{"ADDR", type text}, {"RRUL", type text},{"Custom", type text}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"ExtractedText Before Delimiter" = Table.TransformColumns(#"Changed Type",{{"Custom", each Text.BeforeDelimiter(_, ":"), typetext}}),[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RenamedColumns" = Table.RenameColumns(#"Extracted Text BeforeDelimiter",{{"Custom", "ID"}})[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]in[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    #"RenamedColumns"[/COLOR][/SIZE][/FONT]
 
Last edited:
Upvote 0
Hello gazpage,

Thanks for answer my question. I've been trying but is not working for me. The code for Table0 it works but the rest don't since when I copy all your code in a single Advance Editor window I get error. It seems only allows one let block.

I'm using Excel 2016.

How would be the way to introduce your code in order it works for me?

Regards
 
Upvote 0
Sorry, I ran out of time. Wherever it says // that is a new query that you need to paste into advanced editor on its own. The part directly after the // is the name you need to call the query. I guess you may get some errors until youve done them all because they are obviously interdependent.

lastly, this is only step one of your problem, the transformations. I haven’t solved the merge of table 2.
 
Upvote 0
Sorry, I ran out of time. Wherever it says // that is a new query that you need to paste into advanced editor on its own. The part directly after the // is the name you need to call the query. I guess you may get some errors until youve done them all because they are obviously interdependent.

lastly, this is only step one of your problem, the transformations. I haven’t solved the merge of table 2.
No problem. Thanks for your time.

I was able to set correctly all of your queries except 2. The main part of errors were because of the steps names that have spaces and in next step appeared without space and viceversa or "typetext" instead of "type text"

All of those errors I was able to fix them, but for query "Repeats" I get this error in ChangeType step

Code:
Expression.Error: The column 'RRUL' of the table wasn't found.Details:
    RRUL

and for Output query in step ExpandedCustom.1 I get this
Code:
Expression.Error: The column 'RRUL' of the table wasn't found.Details:
    RRUL

Why these errors?

Thanks. Best regards
 
Upvote 0
If you want to DM me I can send you my file. It's a PBIX, but at least you can copy the actual queries and see how it works. The errors are the same thing, where it isn't producing an RRUL column, but no idea why no.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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
Back
Top