Community Champion

5 years ago

Solved

Request for advice on PQ formula

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!

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
5 years ago
a draft ...
coalesce columns.pbix32 KB

5 Replies

AlexisOlson

Super User

5 years ago

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
Community Champion
5 years ago
Thank you so much for your kind help! Your idea is inspiring; I'm trying to incorporate your proposal into my solution.

Anonymous

Not applicable

5 years ago

a draft ...

coalesce columns.pbix32 KB

CNENFRNL

Community Champion

5 years ago

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

Community Champion

5 years ago

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"

Forum Discussion

Request for advice on PQ formula

5 Replies

Related content

If formula

formula

DAX Formula Help

Matrix Layout Advice

Multiple DAX for WoW, YoY, MoM Advice