Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am currently trying to wrap my head around a little problem that I simply cannot get working properly.
I have a large excel table containing target fulfilment for different entities. The structure of this data is as follows:
Name | ID | Task 1 Description | Task 1 Completed? | Task 2 Description | Task 2 Completed? | Task 3 Description | Task 3 Completed? |
John Smith | 1 | Buy a house | Yes | Build a car | No | Run a marathon | Yes |
Ryan Miller | 2 | Buy a car | No | Plant a tree | Yes | Walk your dog | No |
In my power BI report I however want to show the data in the following format:
[Filtered by John Smith]
Task | Completed? |
Buy a house | Yes |
Build a car | No |
Run a marathon | Yes |
[Filtered by Ryan Miller]
Task | Completed? |
Buy a car | No |
Plant a tree | Yes |
Walk your dog | No |
Does any one know a clever little trick to manipulate the table so that this will work? Or is there a good way to do this in the current format?
The idea behind this is to share targets and their status with different team members. The idea is to have a matrix/table with the information as shown above and a slicer to filter by name.
Thanks in advance!
S
Solved! Go to Solution.
This might help...
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Task 1 Description", "Task 2 Description", "Task 3 Description"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Task", each if Text.Contains([Attribute], "Task 1") then [Task 1 Description]
else if Text.Contains([Attribute], "Task 2") then [Task 2 Description]
else if Text.Contains([Attribute], "Task 3") then [Task 3 Description]
else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Task 1 Description", "Task 2 Description", "Task 3 Description", "Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Task", "Value"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Task] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Completed ?"}})
in
#"Renamed Columns"
Alternative solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIEYqfSSoVEhYz80uJUIC8ytRgslpmTAhRNTiwC8vzygURQaR5QIDexKLEkIz8PqjJWJ1opqDIxT8E3MycnFaTWCG4ikt6AnMS8EqBQSVEqwo7wxJxshcr80iKFlPx0iMrYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, #"Task 1 Description" = _t, #"Task 1 Completed?" = _t, #"Task 2 Description" = _t, #"Task 2 Completed?" = _t, #"Task 3 Description" = _t, #"Task 3 Completed?" = _t]),
Custom1 = List.Transform(
Table.ToRows(Source),
each
let
hdr = List.FirstN(_, 2)
in
Table.FromRows(
List.TransformMany({_}, each List.Split(List.Skip(_, 2), 2), (x, y) => hdr & y),
{"Name", "ID", "Task", "Completed?"}
)
),
Custom2 = Table.Combine(Custom1)
in
Custom2
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks - that worked out perfectly!
This might help...
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Task 1 Description", "Task 2 Description", "Task 3 Description"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Task", each if Text.Contains([Attribute], "Task 1") then [Task 1 Description]
else if Text.Contains([Attribute], "Task 2") then [Task 2 Description]
else if Text.Contains([Attribute], "Task 3") then [Task 3 Description]
else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Task 1 Description", "Task 2 Description", "Task 3 Description", "Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Task", "Value"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Task] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Completed ?"}})
in
#"Renamed Columns"