Get transformed table from 2 input tables PQ

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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:

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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.
 

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top