Get transformed table from 2 input tables PQ

F

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
328
Office Version
  1. 365
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="class: grid, width: 50%, align: center"]
<tbody>[TR]
[TD="colspan: 2, align: center"]TABLE 1[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]TABLE 2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000080, align: center"]DESCRIPTION[/TD]
[TD="bgcolor: #000080, align: center"] VALUE[/TD]
[TD="align: center"]
[/TD]
[TD="bgcolor: #008000, align: center"] PREFIX[/TD]
[TD="bgcolor: #008000, align: center"] CODE[/TD]
[/TR]
[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7235[/TD]
[TD="align: center"] ABX1[/TD]
[/TR]
[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"] JFMSC[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3553[/TD]
[TD="align: center"] POWQ[/TD]
[/TR]
[TR]
[TD="align: center"]TYPE[/TD]
[TD="align: center"] UHELQ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7459[/TD]
[TD="align: center"] UWEER[/TD]
[/TR]
[TR]
[TD="align: center"]DFRUL[/TD]
[TD="align: center"] F4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10012[/TD]
[TD="align: center"] ABX1[/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]10012002[/TD]
[TD="align: center"][/TD]
[TD="align: center"]430[/TD]
[TD="align: center"] ABX1[/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]723[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"] PLLSJS[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]TYPE[/TD]
[TD="align: center"] UHELQ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DFRUL[/TD]
[TD="align: center"] P3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"] AAAARR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]TYPE[/TD]
[TD="align: center"] UHELQ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DFRUL[/TD]
[TD="align: center"] T7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]35531156[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]72358[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]86401[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] K9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"] PPROOA[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]TYPE[/TD]
[TD="align: center"] RRHN[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]DFRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]43001[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] T8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]7459001[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] D4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]430457[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] W2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]745913[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]ADDR[/TD]
[TD="align: center"]74598001[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]RRUL[/TD]
[TD="align: center"] Y5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





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.



[TABLE="width: 70%, align: center"]
<tbody>[TR="bgcolor: #4800FF"]
[TH]ID[/TH]
[TH]NAME[/TH]
[TH]TYPE[/TH]
[TH]DFRUL[/TH]
[TH]ADDR[/TH]
[TH]RRUL[/TH]
[TH]CODE[/TH]
[/TR]
[TR="bgcolor: #FFFB42"]
[TD]0[/TD]
[TD]JFMSC[/TD]
[TD]UHELQ[/TD]
[TD]F4[/TD]
[TD]10012002[/TD]
[TD]P1[/TD]
[TD]ABX1[/TD]
[/TR]
[TR="bgcolor: #FFFB42"]
[TD]0[/TD]
[TD]JFMSC[/TD]
[TD]UHELQ[/TD]
[TD]F4[/TD]
[TD]723[/TD]
[TD]P1[/TD]
[TD]ABX1[/TD]
[/TR]
[TR="bgcolor: #00CE14"]
[TD]2[/TD]
[TD]PLLSJS[/TD]
[TD]UHELQ[/TD]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #99C7CC"]
[TD]4[/TD]
[TD]AAAARR[/TD]
[TD]UHELQ[/TD]
[TD]T7[/TD]
[TD]35531156[/TD]
[TD]P1[/TD]
[TD]POWQ[/TD]
[/TR]
[TR="bgcolor: #99C7CC"]
[TD]4[/TD]
[TD]AAAARR[/TD]
[TD]UHELQ[/TD]
[TD]T7[/TD]
[TD]72358[/TD]
[TD]P1[/TD]
[TD]ABX1[/TD]
[/TR]
[TR="bgcolor: #99C7CC"]
[TD]4[/TD]
[TD]AAAARR[/TD]
[TD]UHELQ[/TD]
[TD]T7[/TD]
[TD]86401[/TD]
[TD]K9[/TD]
[TD]NF[/TD]
[/TR]
[TR="bgcolor: #FFD06D"]
[TD]0[/TD]
[TD]PPROOA[/TD]
[TD]RRHN[/TD]
[TD]P1[/TD]
[TD]43001[/TD]
[TD]T8[/TD]
[TD]ABX1[/TD]
[/TR]
[TR="bgcolor: #FFD06D"]
[TD]0[/TD]
[TD]PPROOA[/TD]
[TD]RRHN[/TD]
[TD]P1[/TD]
[TD]7459001[/TD]
[TD]D4[/TD]
[TD]UWEER[/TD]
[/TR]
[TR="bgcolor: #FFD06D"]
[TD]0[/TD]
[TD]PPROOA[/TD]
[TD]RRHN[/TD]
[TD]P1[/TD]
[TD]430457[/TD]
[TD]W2[/TD]
[TD]ABX1[/TD]
[/TR]
[TR="bgcolor: #FFD06D"]
[TD]0[/TD]
[TD]PPROOA[/TD]
[TD]RRHN[/TD]
[TD]P1[/TD]
[TD]745913[/TD]
[TD]P1[/TD]
[TD]UWEER[/TD]
[/TR]
[TR="bgcolor: #FFD06D"]
[TD]0[/TD]
[TD]PPROOA[/TD]
[TD]RRHN[/TD]
[TD]P1[/TD]
[TD]74598001[/TD]
[TD]Y5[/TD]
[TD]UWEER[/TD]
[/TR]
</tbody>[/TABLE]




Many thanks in advance for any help.
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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
gazpage said:
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.
Click to expand...
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
You must log in or register to reply here.

Similar threads

F
  • Question Question
Transform table with Pivot Table, DAX formula
Replies
4
Views
1K
Fractalis
F
F
  • Question Question
Help with formula tabulate values
Replies
4
Views
812
Fractalis
F

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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