Forum Discussion
Helper ICombine columns into 1
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. 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.
Super UserHi 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"