Need Help! Data transformation

randhir I hope this will help, i'm sure previous solution will also work but I didn't tested those.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOLSpLLfJLzE0FchydnF1c3dBFA/w9QyND0EUDw12DQiKVYnWilYyAXJfM4myojAsq1xWV6wbWYgyyraAgJzM5sSQzPw/I8/H2whDziAwFknmlOTkwKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, COL1 = _t, COL1_Value = _t, COL2 = _t, COL2_Value = _t, COL3 = _t, COL3_Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"COL2", "COL3"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"COL1_Value", "COL2_Value", "COL3_Value"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[COL1]), "COL1", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns2", "Index", 1, 1)
in
    #"Added Index"

HI, Anonymous 

You may try this way:

Duplicate the basic table twice.

Then remove COL1 and COL1_Value for first table, and  remove COL2 and COL2_Value for second table, and  remove COL3 and COL3_Value for yhird table

Then merge table1 with table2 by ID and COL column, expend then merge with table3 again by the same logic.

Now we get the COL2 and COL3 values are in order same as COL1

Then transform it as above.

Best Regards,
Lin

Completely dynamic M

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkst8kvMTVWK1YlWcskszoZzHAsKcjKTE0sy8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Keywords to be searched for" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords to be searched for", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Keywords to be searched for", "Keywords to be searched for"}})
in
    #"Renamed Columns"

Table4

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Need-Help-Data-transformation/m-p/650169#M311784")),
    Data2 = Source{2}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}}),
    Custom1 = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    Custom2 = Table.AlternateRows(#"Transposed Table",1,1,1),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Custom2, {"Column1"}, "Attribute", "Value"),
    Custom3 = Table.AlternateRows(#"Transposed Table",0,1,1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom3, {"Column1"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","_Value","",Replacer.ReplaceText,{"Column1"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"Column1", "Attribute"},#"Unpivoted Other Columns1",{"Column1", "Attribute"},"Replaced Value",JoinKind.LeftOuter),
    #"Expanded Replaced Value" = Table.ExpandTableColumn(#"Merged Queries", "Replaced Value", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Replaced Value",{"Column1", "Value", "Value.1"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Column1"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate(
[AD][Value.1],
"",
(state,current)=> (state &" "& current)
)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded AD" = Table.ExpandTableColumn(#"Removed Columns1", "AD", {"Column1", "Value", "Value.1"}, {"Column1", "Value", "Value.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AD", "Custom.1", each let 
   CurrentText = [Custom],
   Result= Table.SelectRows(Table4,each not Text.Contains(CurrentText, [Keywords to be searched for], Comparer.OrdinalIgnoreCase))

in
  Result),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Keywords to be searched for"}, {"Keywords to be searched for"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [Value.1]="" and [Keywords to be searched for]<>"" then [Keywords to be searched for] else [Value.1]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Column1", "Custom.1", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Custom.1]), "Custom.1", "Value")
in
    #"Pivoted Column"

Desired Output