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

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
Icey
Community Support
Community Support

Hi @Anonymous ,

 

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
Icey
Community Support
Community Support

Hi @Anonymous ,

 

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.

 

Anonymous
Not applicable

Hi@Icey ,

This is exactly what I was looking for.

Thanks a million, mate.

Kind regards,

Chandu

amitchandak
Super User
Super User

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

 

speedramps
Super User
Super User

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?

Anonymous
Not applicable

@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

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.