Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI users,
I am building a workload/capacity KPI and need help.
See the current table :
I need to create a new table, like this one below::
As you can, see I would like to have a table showing each period (WW) between the different gates.
Could you help me please?
Thank you,
@Anonymous Like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUkYGRoaGxiAGlGNsCWSYQThGRsZKsTrRYDnv1LyUokSYIhMgwxSqyABknDmUY2gA1gEyMTIxLy8zORuqBazKEKoKrN8SyjE1AmsxQbfEFGStMUyHBYJjDHJWLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Name" = _t, #"Gate A" = _t, #"Hrs A-B" = _t, #"Gate B" = _t, #"Hrs A-C" = _t, #"Gate C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource Name", type text}, {"Gate A", Int64.Type}, {"Hrs A-B", Int64.Type}, {"Gate B", Int64.Type}, {"Hrs A-C", Int64.Type}, {"Gate C", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project", "Resource Name", "Hrs A-B", "Hrs A-C"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Period"}})
in
#"Renamed Columns"
@Greg_Deckler , would you be so kind to do it directly in PowerBi instead? I have attached a link to the PowerBI file:
https://1drv.ms/u/s!AkgzJ_JqFOgeiINLHKYzLzkeKnHVTQ?e=77eu0J
Thanks very much!
@Anonymous Are you saying not in Power Query but in DAX and/or a visual?
@Greg_Deckler , I am sorry this is not what I meant. I tried it in Power Query, but I am not sure it would work. Wouldn't be rather a DAX function that could do show one line for each period? See for example what it would looks like for project 1 :
Project | Ressource name | Period | Hrs A-B | Hrs A-C |
1 | John Smith | 202113 | 2 | 0 |
1 | John Smith | 202114 | 2 | 0 |
1 | John Smith | 202115 | 2 | 0 |
1 | John Smith | 202116 | 2 | 0 |
1 | John Smith | 202117 | 2 | 0 |
1 | John Smith | 202118 | 2 | 0 |
1 | John Smith | 202119 | 2 | 0 |
1 | John Smith | 202120 | 2 | 0 |
1 | John Smith | 202121 | 2 | 0 |
1 | John Smith | 202122 | 2 | 0 |
1 | John Smith | 202123 | 2 | 0 |
1 | John Smith | 202124 | 2 | 0 |
1 | John Smith | 202125 | 2 | 0 |
1 | John Smith | 202126 | 2 | 0 |
1 | John Smith | 202127 | 2 | 0 |
1 | John Smith | 202128 | 2 | 0 |
1 | John Smith | 202129 | 2 | 0 |
1 | John Smith | 202130 | 2 | 0 |
1 | John Smith | 202131 | 2 | 0 |
1 | John Smith | 202132 | 2 | 0 |
1 | John Smith | 202133 | 2 | 0 |
1 | John Smith | 202134 | 2 | 0 |
1 | John Smith | 202135 | 2 | 0 |
1 | John Smith | 202136 | 2 | 0 |
1 | John Smith | 202137 | 2 | 0 |
1 | John Smith | 202138 | 2 | 0 |
1 | John Smith | 202139 | 0 | 6 |
1 | John Smith | 202140 | 0 | 6 |
1 | John Smith | 202141 | 0 | 6 |
1 | John Smith | 202142 | 0 | 6 |
1 | John Smith | 202143 | 0 | 6 |
1 | John Smith | 202144 | 0 | 6 |
1 | John Smith | 202145 | 0 | 6 |
1 | John Smith | 202146 | 0 | 6 |
1 | John Smith | 202147 | 0 | 6 |
1 | John Smith | 202148 | 0 | 6 |
1 | John Smith | 202149 | 0 | 6 |
1 | John Smith | 202150 | 0 | 6 |
1 | John Smith | 202151 | 0 | 6 |
1 | John Smith | 202152 | 0 | 6 |
1 | John Smith | 202201 | 0 | 6 |
@Anonymous You would need to use GENERATE or GENERATESERIES for that.
@Anonymous , is it something like unpivot twice?
https://kohera.be/power-bi/how-to-unpivot-twice/
@amitchandak , I tried the link above but I don't think it will work for my request. There was an error in by first screenshot. There is also project I/D on column 1 :
Is there a way to list all periods associated with a project/ressource and showing the hrs/wk for each?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |