Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Details | Mike | Denis | Mary | Sally | Peter | ||
2 | Paris | Y | y | |||||
3 | London | Y | Y | |||||
4 | Joburg | Y | ||||||
5 | Sydney | y | ||||||
6 | 15-21 | Y | y | |||||
7 | 22-30 | Y | ||||||
8 | 31-50 | Y | y | |||||
9 | Doctor | Y | y | |||||
10 | Lawyer | y | ||||||
11 | Singer | Y | ||||||
12 | Soldier | Y | y | |||||
13 | Spy | Y | ||||||
Sheet1 |
Of course, but with an exclusionary condition of each slicer.
Your table is not normalized and needs many transformations.my data is laid out a little differently
If that's the result you want to get, I can try to explain the steps to get all those tables in another message.The result i want is the same as you posted. three separate slicers. One for location, one for Ages, and then one for skills. any ideas.
let
Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text)
in
#"Inserted Merged Column"
sorry Pedro. Thanks for the above. I got pulled onto something else for the time being but will have a look in the evenings over the next few days. Not sure about Power Query. I can add individual parts of PQ to my quick access toolbar but not the whole menu.As you have not yet answered the questions I asked, I am sending you the M formulas in Power Query of my first solution to your problem (follow my signature below) so that you can test them.
Power Query:let Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text) in #"Inserted Merged Column"
What version of Excel do you have installed?Not sure about Power Query. I can add individual parts of PQ to my quick access toolbar but not the whole menu.
let
Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type", "Details"}, "Name", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),
#"Group Clust Index" = Table.Group( #"Removed Columns",List.RemoveItems(Table.ColumnNames(#"Removed Columns"),{"Details"})
,{"ColOfTables",each Table.AddIndexColumn(_,"idx")}),
#"Expanded ColOfTables" = Table.ExpandTableColumn(#"Group Clust Index", "ColOfTables", {"Details", "idx"}, {"Details", "idx"}),
#"Sorted Rows" = Table.Sort(#"Expanded ColOfTables",{{"idx", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Type]), "Type", "Details"),
#"Removed Index" = Table.RemoveColumns(#"Pivoted Column",{"idx"}),
#"Filled Down" = Table.FillDown(#"Removed Index",{"Locations", "Ages", "Skills"})
in
#"Filled Down"