Combine columns into 1

griffinst What you can do is duplicate your query. In one query, remove the PE columns and unpivot. In the other query, remove the GIC columns and unpivot. Then append the tables together.

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"State", type any}, {"Sales", Int64.Type}, {"GIC: Hypertension", Int64.Type}, {"GIC: Cancer", Int64.Type}, {"GIC: Obesity", Int64.Type}, {"PE: Knee Replacement", Int64.Type}, {"PE: Financial", Int64.Type}, {"PE: Social", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer ID", "State", "Sales"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

Hope this helps.

Hi griffinst 

I am not sure what your expected result is as you did not post your expected result but try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQCJR0lIDI2NQCSIA4mHasTrWQEBBCVpgYGWFWBaJBKYyCAqDQyQJZFZcfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, State = _t, Sales = _t, #"GIC: Hypertension" = _t, #"GIC: Cancer" = _t, #"GIC: Obesity" = _t, #"PE: Knee Replacement" = _t, #"PE: Financial" = _t, #"PE: Social" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"State", type text}, {"Sales", Int64.Type}, {"GIC: Hypertension", Int64.Type}, {"GIC: Cancer", Int64.Type}, {"GIC: Obesity", Int64.Type}, {"PE: Knee Replacement", Int64.Type}, {"PE: Financial", Int64.Type}, {"PE: Social", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "PE", each Table.FromColumns(
    {
        // Column names
        List.Select(Table.ColumnNames(#"Added Index"), each Text.StartsWith(_, "PE:")),

        // Values for the current row
        List.Transform(
            List.Select(Table.ColumnNames(#"Added Index"), each Text.StartsWith(_, "PE:")),
            (col) => Record.Field(_, col)
        )
    },
    {"PE", "PE Value"}
)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "GIC", each Table.FromColumns(
    {
        // Column names
        List.Select(Table.ColumnNames(#"Added Index"), each Text.StartsWith(_, "GIC:")),

        // Values for the current row
        List.Transform(
            List.Select(Table.ColumnNames(#"Added Index"), each Text.StartsWith(_, "GIC:")),
            (col) => Record.Field(_, col)
        )
    },
    {"GIC", "GIC Value"}
)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"GIC: Hypertension", "GIC: Cancer", "GIC: Obesity", "PE: Knee Replacement", "PE: Financial", "PE: Social"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each let
    PE_Table = Record.Field(_, "PE"),
    GIC_Table = Record.Field(_, "GIC")
in
    Table.FromColumns(
        {
            Table.Column(PE_Table, "PE"),
            Table.Column(PE_Table, "PE Value"),
            Table.Column(GIC_Table, "GIC"),
            Table.Column(GIC_Table, "GIC Value")
        },
        {"PE", "PE Value", "GIC", "GIC Value"}
    )),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"PE", "GIC"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"PE", "PE Value", "GIC", "GIC Value"}, {"PE", "PE Value", "GIC", "GIC Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom","PE: ","",Replacer.ReplaceText,{"PE"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","GIC: ","",Replacer.ReplaceText,{"GIC"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"PE Value", Int64.Type}, {"GIC Value", Int64.Type}})
in
    #"Changed Type1"

Hi griffinst,

Thank you danextian wardy912  GeraldGEmerick  and Ashish_Mathur for your responses to the query.

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.

Hi griffinst,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.