Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community,
We want to report on Primavera (P6) data in Power BI.
I am getting the data in .xlsx or .csv format. I am trying to shape the data in Power Query.
It is located at
https://docs.google.com/spreadsheets/d/1AI5_zDXGX6q18vAWLN5LIoOK5IZDEi0XCVrEHlmBQrY/edit?usp=sharing
I am getting Start and Finish Date columns and BaseLine Start and BaseLine Finish Date Columns.
There is a string 'A' next to some of the dates in the Start and BaseLine Start Dates which the business said is Actual Start Date (highlighted in Yellow in the attached file).
Therefore, I have decided to Split this column into two columns, one is Start Date that will contain only the Dates without 'A' and the other is going to be Actual Start Date that will contain only the Dates with 'A'. Same will the be the case with the BaseLine Start Actual Start Date too (highlighted in Green in the attached file).
I have tried the split and extract options but it is not getting me to what I wanted though! I have also tried a few M and DAX but could not figure it out.
I have a strong feeling that DAX will be able to get me what I wanted. Can anybody please shed some light on how to achieve this?
There will be various other zones and subzones and I would be reporting on completion status and status and also would be interested in overdue dates, variations, etc which are pretty standard for Planning reporting.
Any help/guidance on how to achieve this is greatly appreciated.
Any info with screenshots or guidelines would be of great help to me and others in a similar situation.
Hoping to hear soon.
Thanks for your time in advance.
Regards,
Chandu
Solved! Go to Solution.
Hi @Anonymous ,
Please check if this is what you want:
Attach the complete code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWdUtN0jUyUIrViVYyNtD1Ty7RNbRUcATzjcyRZY0MwDxDC5BsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Start Date", each if not Text.Contains([Start], "A") then [Start] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Actual Start Date", each if Text.Contains([Start], "A") then [Start] else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column1"," A","",Replacer.ReplaceText,{"Actual Start Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Start Date", type date}, {"Actual Start Date", type date}})
in
#"Changed Type1"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please check if this is what you want:
Attach the complete code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWdUtN0jUyUIrViVYyNtD1Ty7RNbRUcATzjcyRZY0MwDxDC5BsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Start Date", each if not Text.Contains([Start], "A") then [Start] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Actual Start Date", each if Text.Contains([Start], "A") then [Start] else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column1"," A","",Replacer.ReplaceText,{"Actual Start Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Start Date", type date}, {"Actual Start Date", type date}})
in
#"Changed Type1"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am not sure I got it completely.
To split column in edit query/transform data mode
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
https://community.powerbi.com/t5/Desktop/Splitting-multiple-columns-using-delimiter/td-p/438358
In M you have text.split function, that will give the list and you can convert list to columns.
Your date of same size DD-MMM-YY , Text.start in M and left in Dax can give the results
new date = left(table[date],9)
https://docs.microsoft.com/en-us/powerquery-m/text-start
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Hi Giftedbrain
Your input file has columns with
Use power query “M” extrat with delimiters to split the Input date to
Then use power query “M” to add conditional columns
You can then delete
Create a DimDate table (see how on the community forum)
Create an inactive date relationship with Tentative date
Create an inactive date relationship with Actual date
Then create DAX measures using USERELATIONSHIP with Tentative or Actual date.
These will just return values based on the Tentative or Actual date.
Hope this helps?
@speedrampsHi mate,
Thanks for your quick response. I don't see any options under Extract after selecting the 'Start Date' column that read as 'Extract with Delimiters to split'. Attached are the only options I have in query editor.
Can you please be more specific and if possible post some screenshots in here so I know what you're referring to?
Sorry for the trouble, your time is greatly appreciated!
Thanks,
Chandu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |