none
How to "Merge Queries" to get output in Power Query RRS feed

  • Question

  • Hello to all,

    I have this input table.

    STATE CITY
    California San Diego
    Florida Tampa
    Colorado Colorado Springs
    California San Jose
    Alaska Anchorage
    Alabama Mobile
    California Fresno
    California Los Angeles
    Colorado Aurora
    Colorado Fort Collins
    California San Francisco
    Florida Miami
    California Sacramento
    Florida Jacksonville
    Alabama Birmingham
    Georgia Atlanta
    Alabama Huntsville
    Alaska Fairbanks
    Alabama Montgomery
    Colorado Denver


    I've applied the following steps:
    1-) Filter by California and Florida
    2-) Add index column

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each ([STATE] = "California" or [STATE] = "Florida")),
        #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index.1", 1, 1)
    in
        #"Added Index"


    And currently I have this table:

    STATE CITY Index
    California San Diego 1
    Florida Tampa 2
    California San Jose 3
    California Fresno 4
    California Los Angeles 5
    California San Francisco 6
    Florida Miami 7
    California Sacramento 8
    Florida Jacksonville 9

    Now I want to get the following output that I think can be done with "Merge Queries" but I don't know how to apply that step  in order to get this.

    STATE CITY Index
    California San Diego 1
    Florida Tampa 2
    Colorado Colorado Springs null
    California San Jose 3
    Alaska Anchorage null
    Alabama Mobile null
    California Fresno 4
    California Los Angeles 5
    Colorado Aurora null
    Colorado Fort Collins null
    California San Francisco 6
    Florida Miami 7
    California Sacramento 8
    Florida Jacksonville 9
    Alabama Birmingham null
    Georgia Atlanta null
    Alabama Huntsville null
    Alaska Fairbanks null
    Alabama Montgomery null
    Colorado Denver null

    Thanks for any help.


    • Edited by cgkmal Monday, April 1, 2019 9:14 PM
    Monday, April 1, 2019 9:10 PM

Answers

  • My goal was to maintain the original order of your source. Your solution doesn't do so, but if it works for you, that's great!

    Another way to skin the cat (while maintaining original order):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedSortIndex = Table.AddIndexColumn(Source, "Sort Index"),
        filteredRows1 = Table.SelectRows(addedSortIndex, each List.Contains({"California", "Florida"}, [STATE])),
        addedIndex = Table.AddIndexColumn(filteredRows1, "Index", 1),
        filteredRows2 = Table.SelectRows(addedSortIndex, each not List.Contains({"California", "Florida"}, [STATE])),
        combinedTables = Table.Combine({addedIndex, filteredRows2}),
        sortedRows = Table.Sort(combinedTables,{{"Sort Index", Order.Ascending}}),
        removedSortIndex = Table.RemoveColumns(sortedRows,{"Sort Index"})
    in
        removedSortIndex

    Edit: In your solution, you can sort the Index column before removing it. Good work!
    • Edited by Colin Banfield Tuesday, April 2, 2019 1:53 PM
    • Marked as answer by cgkmal Tuesday, April 2, 2019 3:10 PM
    Tuesday, April 2, 2019 1:37 PM
  • The following is one possibility:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedSortIndex = Table.AddIndexColumn(Source, "Sort Index"),
        addedGroupingColumn = Table.AddColumn(
            addedSortIndex, 
            "Group Column", 
            each if List.Contains({"California", "Florida"}, [STATE]) then 0 else null
        ),
        groupedRows = Table.Group(
            addedGroupingColumn, 
            {"Group Column"}, 
            {{"Tables", each Table.AddIndexColumn(_, "Index", 1), type table}}
        ),
        combinedTables = Table.Combine(groupedRows[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Sort Index", Order.Ascending}}),
        replacedValues = Table.ReplaceValue(
            sortedRows, 
            each [Index], 
            each if [Group Column] = 0 then [Index] else null,
            Replacer.ReplaceValue,
            {"Index"}
        ),
        removedColumns = Table.RemoveColumns(replacedValues,{"Sort Index", "Group Column"})
    in
        removedColumns

    • Marked as answer by cgkmal Tuesday, April 2, 2019 6:30 AM
    • Unmarked as answer by cgkmal Tuesday, April 2, 2019 6:31 AM
    • Marked as answer by cgkmal Tuesday, April 2, 2019 6:31 AM
    Tuesday, April 2, 2019 2:57 AM

All replies

  • The following is one possibility:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedSortIndex = Table.AddIndexColumn(Source, "Sort Index"),
        addedGroupingColumn = Table.AddColumn(
            addedSortIndex, 
            "Group Column", 
            each if List.Contains({"California", "Florida"}, [STATE]) then 0 else null
        ),
        groupedRows = Table.Group(
            addedGroupingColumn, 
            {"Group Column"}, 
            {{"Tables", each Table.AddIndexColumn(_, "Index", 1), type table}}
        ),
        combinedTables = Table.Combine(groupedRows[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Sort Index", Order.Ascending}}),
        replacedValues = Table.ReplaceValue(
            sortedRows, 
            each [Index], 
            each if [Group Column] = 0 then [Index] else null,
            Replacer.ReplaceValue,
            {"Index"}
        ),
        removedColumns = Table.RemoveColumns(replacedValues,{"Sort Index", "Group Column"})
    in
        removedColumns

    • Marked as answer by cgkmal Tuesday, April 2, 2019 6:30 AM
    • Unmarked as answer by cgkmal Tuesday, April 2, 2019 6:31 AM
    • Marked as answer by cgkmal Tuesday, April 2, 2019 6:31 AM
    Tuesday, April 2, 2019 2:57 AM
  • Hello Colin, I hope you're fine!

    Thanks so much for help me again. I learn from your solutions. I see you use custom formulas and I'm still not able to do that, but I see other logic and ways to solve the problems.

    This time I was close, but I was doing wrong the "Merged Queries" step, but seeing some example of the Table.NestedJoin() helped me to get this solution. Thanks again :)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
        AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Filtered Rows" = Table.SelectRows(AddedIndex, each ([STATE] = "California" or [STATE] = "Florida")),
        AddedIndex1 = Table.AddIndexColumn(#"Filtered Rows", "Custom", 1, 1),
        #"Merged Queries" = Table.NestedJoin(AddedIndex,{"STATE", "Index"},AddedIndex1,{"STATE", "Index"},"AddedIndex1",JoinKind.LeftOuter),
        #"Expanded AddedIndex1" = Table.ExpandTableColumn(#"Merged Queries", "AddedIndex1", {"Custom"}, {"Index1"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded AddedIndex1",{"Index"})
    in
        #"Removed Columns"

    Best regards


    • Edited by cgkmal Tuesday, April 2, 2019 6:36 AM
    Tuesday, April 2, 2019 6:34 AM
  • My goal was to maintain the original order of your source. Your solution doesn't do so, but if it works for you, that's great!

    Another way to skin the cat (while maintaining original order):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedSortIndex = Table.AddIndexColumn(Source, "Sort Index"),
        filteredRows1 = Table.SelectRows(addedSortIndex, each List.Contains({"California", "Florida"}, [STATE])),
        addedIndex = Table.AddIndexColumn(filteredRows1, "Index", 1),
        filteredRows2 = Table.SelectRows(addedSortIndex, each not List.Contains({"California", "Florida"}, [STATE])),
        combinedTables = Table.Combine({addedIndex, filteredRows2}),
        sortedRows = Table.Sort(combinedTables,{{"Sort Index", Order.Ascending}}),
        removedSortIndex = Table.RemoveColumns(sortedRows,{"Sort Index"})
    in
        removedSortIndex

    Edit: In your solution, you can sort the Index column before removing it. Good work!
    • Edited by Colin Banfield Tuesday, April 2, 2019 1:53 PM
    • Marked as answer by cgkmal Tuesday, April 2, 2019 3:10 PM
    Tuesday, April 2, 2019 1:37 PM
  • Hello Colin,

    Actually I didn't notice about the sorting. Thanks for let me know and fix it and for share another way to do it. I have your example now in how to use Combine Tables.

    Many thanks again.

    Tuesday, April 2, 2019 3:10 PM