Combine columns into 1

[griffinst]

I have a dataset.  I need to somehow combine the "GIC" into a column and the "PE" into another column so I can drop them into slicers for filtering.  I was able to transpose, but it doesn't allow me to transpose the GIC and PE seperately in power query.  looking for some solutions.   the "1" and "0" values for those are basically just "true" or "false".

Customer ID	State	Sales	GIC: Hypertension	GIC: Cancer	GIC: Obesity	PE: Knee Replacement	PE: Financial	PE: Social
1111		350	1	0	1	0	1	0
2222		500	0	1	0	1	0	1
3333		200	1	0	0	1	0	0

[wardy912 · Accepted solution]

Hi @griffinst 

 You need to unpivot here rather than transpose:

In Power Query, select all of the columns that you want to keep fixed, then select 'unpivot other columns'

Select the 'Attribute' column, then on the home tab select 'Split Column by Delimiter'

Then split by colon, left-most delimiter

You get 2 new columns, call Attribute.1 "Category" and Attribute.2 "Condition".

Close and apply.

Now you can add a visual level filter of Value=1 (or filter out 0s in power query) and a category slicer to display your results!

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[danextian]

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"

[Ashish_Mathur]

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.

[griffinst · Accepted solution]

@GeraldGEmerick.  I think that might work but I think I need a merge rather then an append.  The split column worked but I'm not getting the results I need.  Pretty sure I need Columns of "GIC" and "PE" to filter the data correctly.  So then a user can select a GIC filter and a PE filter and both will filter the dataset down to the people who meet both criteria.

see below screenshot... I need the "category" values to be seperate columns with values of "Cat_Detail" then the cooresponding "Value".   ie...   "Gap in Care" column and "Gap in Care Value" column.  and same for "uncontrolled.., and preventable...  Thanks for any help you can provide.

[wardy912 · Accepted solution]

Hi @griffinst 

 You need to unpivot here rather than transpose:

In Power Query, select all of the columns that you want to keep fixed, then select 'unpivot other columns'

Select the 'Attribute' column, then on the home tab select 'Split Column by Delimiter'

Then split by colon, left-most delimiter

You get 2 new columns, call Attribute.1 "Category" and Attribute.2 "Condition".

Close and apply.

Now you can add a visual level filter of Value=1 (or filter out 0s in power query) and a category slicer to display your results!

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[GeraldGEmerick]

@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.