[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]