Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

New table - list between two dates

Dear Power BI users, 

 

I am building a workload/capacity KPI and need help. 

 

See the current table : 

 

Antoine102_0-1631275277177.png

 

I need to create a new table, like this one below::

 

Antoine102_1-1631275277643.png

 

 

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, 

7 REPLIES 7
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

ProjectRessource namePeriodHrs A-BHrs A-C
1John Smith20211320
1John Smith20211420
1John Smith20211520
1John Smith20211620
1John Smith20211720
1John Smith20211820
1John Smith20211920
1John Smith20212020
1John Smith20212120
1John Smith20212220
1John Smith20212320
1John Smith20212420
1John Smith20212520
1John Smith20212620
1John Smith20212720
1John Smith20212820
1John Smith20212920
1John Smith20213020
1John Smith20213120
1John Smith20213220
1John Smith20213320
1John Smith20213420
1John Smith20213520
1John Smith20213620
1John Smith20213720
1John Smith20213820
1John Smith20213906
1John Smith20214006
1John Smith20214106
1John Smith20214206
1John Smith20214306
1John Smith20214406
1John Smith20214506
1John Smith20214606
1John Smith20214706
1John Smith20214806
1John Smith20214906
1John Smith20215006
1John Smith20215106
1John Smith20215206
1John Smith20220106

 

 

 

 

 

 

@Anonymous You would need to use GENERATE or GENERATESERIES for that.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@Anonymous , is it something like unpivot twice?

 

https://kohera.be/power-bi/how-to-unpivot-twice/

Anonymous
Not applicable

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

 

Antoine102_0-1631278875803.png

 

Is there a way to list all periods associated with a project/ressource and showing the hrs/wk for each?

 

Antoine102_1-1631278893320.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.