cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giftedbrain Advocate II
Advocate II

Split Date Columns with a string 'A' at the end into two columns.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Split Date Columns with a string 'A' at the end into two columns.

Hi @giftedbrain ,

 

Please check if this is what you want:

 

split.gif

 

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.

 

View solution in original post

5 REPLIES 5
speedramps Helper I
Helper I

Re: Split Date Columns with a string 'A' at the end into two columns.

Hi Giftedbrain

Your input file has columns with

  • Input date dd-mm-yy  X     where   X = A is actual   and X = blank  is  tentative

Use power query “M” extrat with delimiters to split the Input date to

  • Temp Date dd/mm/yyyy
  • Status flag X

Then use power query “M” to add conditional columns          

  • Tentative date = dd/mm/yyyy when Status flag  = blank
  • Actual date = dd/mm/yyyy when Status flag  = blank

You can then delete

  • Input date
  • Temp Date
  • Status flag

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?

Super User IV
Super User IV

Re: Split Date Columns with a string 'A' at the end into two columns.

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

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
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
Proud to be a Super User! Linkedin


Highlighted
giftedbrain Advocate II
Advocate II

Re: Split Date Columns with a string 'A' at the end into two columns.

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

2020-02-17_15-27-48.png

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

Community Support
Community Support

Re: Split Date Columns with a string 'A' at the end into two columns.

Hi @giftedbrain ,

 

Please check if this is what you want:

 

split.gif

 

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.

 

View solution in original post

giftedbrain Advocate II
Advocate II

Re: Split Date Columns with a string 'A' at the end into two columns.

Hi@Icey ,

This is exactly what I was looking for.

Thanks a million, mate.

Kind regards,

Chandu

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors