Request for advice on PQ formula

[CNENFRNL]

Dear PQ gurus,

I'm looking to make such substitutions in PQ but all formulae failed.

value "foo" in columns are substituted by values in corresponding columns, eg. "foo" in ColA are replaced by values in x.ColA, "foo" in ColB by values in x.ColB, etc.

Any hint or advice is highly appreciated!

[AlexisOlson · Accepted solution]

You could do this with some fancy row transformations similar to the syntax I used here but, as mentioned there, it's probably easier to unpivot, replace values, pivot back.

Edit: Since you aren't replacing with a fixed value, it's a bit more involved but the unpivoting and pivoting is still a good trick. Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type any}, {"xx", Int64.Type}, {"ColB", type any}, {"ColC", type any}, {"yy", Int64.Type}, {"ColD", type any}, {"x.ColA", Int64.Type}, {"x.ColB", Int64.Type}, {"x.ColC", Int64.Type}, {"x.ColD", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"xx", "yy", "Index"}, "ColName", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "ColPair", each Text.Replace([ColName],"x.",""), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"xx", "yy", "ColPair"}, {{"Value", each List.Min([Value]), type any}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"xx", "yy", "ColPair"}, #"Grouped Rows", {"xx", "yy", "ColPair"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Value", "ColPair"}),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Removed Columns", "Grouped Rows", {"Value"}, {"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Grouped Rows", List.Distinct(#"Expanded Grouped Rows"[ColName]), "ColName", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Index", "ColA", "xx", "ColB", "ColD", "ColC", "yy", "x.ColA", "x.ColB", "x.ColC", "x.ColD"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns1"

There are some extra steps to make sure the rows and columns are sorted correctly.

If you're OK with three levels of expression context, then you can use the row and record transformation method with fewer steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transformed = Table.FromRecords(
        Table.TransformRows(
            Source,
            (row) => 
            Record.TransformFields(
                row,
                List.Transform(
                    {"ColA", "ColB", "ColC", "ColD"},
                    (col) => {col, each if _ = "foo" then Record.Field(row, "x." & col) else _}
                )
            )
        ),
        Value.Type(Source)
    ),
    #"Reordered Columns" =
        Table.ReorderColumns(
            Transformed,
            {"ColA", "xx", "ColB", "ColC", "yy", "ColD", "x.ColA", "x.ColB", "x.ColC", "x.ColD"}
        )
in
    #"Reordered Columns"

[Anonymous · Accepted solution]

a draft ...

[CNENFRNL]

Thank you so much for your kind help! Your idea is inspiring. I tweak your code and get an idealest solution so far.

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY6xDsAgCET/hdmhRNH6LYS1q/+/FeFsmvDwLhxEVWpUaILb6Q5f3gYI08mK0rOWF4Jh8sVaqn1QkOGz5rpilo4/NeBa6J0/35g/Ki4KZkJmLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, xx = _t, ColB = _t, ColC = _t, yy = _t, ColD = _t, x.ColA = _t, x.ColB = _t, x.ColC = _t, x.ColD = _t]),

    namesX=List.Select(Table.ColumnNames(Origine), each Text.StartsWith(_, "Col")),
    tac=List.Accumulate(
        namesX,
        Origine,
        (s,c) => Table.ReplaceValue(s, each if Text.Contains(Record.Field(_, c), "foo") then Record.Field(_, c) else null, each Record.Field(_,"x."&c), Replacer.ReplaceValue, {c})
    )
in
    tac

[CNENFRNL]

Une autre solution améliorée,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY6xDsAgCET/hdmhRNH6LYS1q/+/FeFsmvDwLhxEVWpUaILb6Q5f3gYI08mK0rOWF4Jh8sVaqn1QkOGz5rpilo4/NeBa6J0/35g/Ki4KZkJmLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, xx = _t, ColB = _t, ColC = _t, yy = _t, ColD = _t, x.ColA = _t, x.ColB = _t, x.ColC = _t, x.ColD = _t]),

    namesX=List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Col")),
    #"Merged Columns" = List.Accumulate(
        namesX,
        Source,
        (s,c) => Table.CombineColumns(s, {c, "x."&c}, each if Text.Contains(_{0}, "foo") then _{1} else _{0}, c)
    )
in
    #"Merged Columns"

[AlexisOlson · Accepted solution]

You could do this with some fancy row transformations similar to the syntax I used here but, as mentioned there, it's probably easier to unpivot, replace values, pivot back.

Edit: Since you aren't replacing with a fixed value, it's a bit more involved but the unpivoting and pivoting is still a good trick. Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type any}, {"xx", Int64.Type}, {"ColB", type any}, {"ColC", type any}, {"yy", Int64.Type}, {"ColD", type any}, {"x.ColA", Int64.Type}, {"x.ColB", Int64.Type}, {"x.ColC", Int64.Type}, {"x.ColD", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"xx", "yy", "Index"}, "ColName", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "ColPair", each Text.Replace([ColName],"x.",""), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"xx", "yy", "ColPair"}, {{"Value", each List.Min([Value]), type any}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"xx", "yy", "ColPair"}, #"Grouped Rows", {"xx", "yy", "ColPair"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Value", "ColPair"}),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Removed Columns", "Grouped Rows", {"Value"}, {"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Grouped Rows", List.Distinct(#"Expanded Grouped Rows"[ColName]), "ColName", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Index", "ColA", "xx", "ColB", "ColD", "ColC", "yy", "x.ColA", "x.ColB", "x.ColC", "x.ColD"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns1"

There are some extra steps to make sure the rows and columns are sorted correctly.

If you're OK with three levels of expression context, then you can use the row and record transformation method with fewer steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transformed = Table.FromRecords(
        Table.TransformRows(
            Source,
            (row) => 
            Record.TransformFields(
                row,
                List.Transform(
                    {"ColA", "ColB", "ColC", "ColD"},
                    (col) => {col, each if _ = "foo" then Record.Field(row, "x." & col) else _}
                )
            )
        ),
        Value.Type(Source)
    ),
    #"Reordered Columns" =
        Table.ReorderColumns(
            Transformed,
            {"ColA", "xx", "ColB", "ColC", "yy", "ColD", "x.ColA", "x.ColB", "x.ColC", "x.ColD"}
        )
in
    #"Reordered Columns"

[CNENFRNL]

Thank you so much for your kind help! Your idea is inspiring; I'm trying to incorporate your proposal into my solution.