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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Eddistoff
New Member

Problems with format dates entered

Hi everyone, 

I need your assistance please, I am barely new using PBI and I have a situation with dates. There is a template of excel fulfilled by people who manage a process, so I required to get the amount of days it takes to approve a process. The problem is they have to enter manually the date, some of them entered the date in a format which is not correct, and others do it correctly.

This is an example I created, so I have I tried to solve it with Power BI, but with no succees. I finally do it manually everytime I require to update the report, just by adding a column with difference of days which result should not take longer than a month:

Date of registrationProductDate of approvalComments Format entered
04/05/2022105/05/2022CorrectDD-MM-YYYY
09/08/2022208/10/2022WrongMM-DD-YYYY
04/08/2022309/04/2022WrongMM-DD-YYYY
01/02/2022405/02/2022CorrectDD-MM-YYYY
09/05/2022505/09/2022WrongMM-DD-YYYY
09/07/2022610/07/2022CorrectDD-MM-YYYY
03/05/2022705/10/2022WrongMM-DD-YYYY
13/02/2022814/02/2022CorrectDD-MM-YYYY
21/04/2022922/04/2022CorrectDD-MM-YYYY

 

Example of manual solution for first example:

Date of approval - date of registration = amount of days 

05-05-2022 - 04-05-2022 = 1

 

Thanks in advance!!

Edgardo Y.

 

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Eddistoff ,

 

Add this as a new custom column to restate the approval date in the correct format:

let
    __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
in
    if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
    then Text.Combine(
        {
            Text.Middle([Date of approval], 3, 2),
            Text.Start([Date of approval], 2),
            Text.End([Date of approval], 4)
        },
        "/"
    )
    else [Date of approval]

 

From here you can change the data type of your new column to Date Type and perform your calculation.

 

Example as working query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBC8IwDIX/S88bTdLMrWd33Vlk7CTizUHx/+Mrri4HpSuENo98fUnm2ZF66ryQiGscI5DtwnlN6X574TWO7TS1Vxy3NMCip6FU5UDGVIRLWp8P3CDA7ZAaKGQIv2gNYk9SanRrUA41+B2j27BY8wLUl5pTXggZ4b9XMF79x6u6DA5mjCF76ZG5hM3KYl6+GOE3trwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of registration" = _t, Product = _t, #"Date of approval" = _t, #"Comments " = _t, #"Format entered" = _t]),
    chgRegDateType = Table.TransformColumnTypes(Source,{{"Date of registration", type date}}),
    addDtApproval =
    Table.AddColumn(
        chgRegDateType,
        "dtApproval", each
        let
            __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
        in
        if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
        then Text.Combine(
            {
                Text.Middle([Date of approval], 3, 2),
                Text.Start([Date of approval], 2),
                Text.End([Date of approval], 4)
            },
            "/"
        )
        else [Date of approval]
    )
in
    addDtApproval

 

Working query output:

BA_Pete_0-1673275424704.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Eddistoff
New Member

Thanks a lot for yout help!!!!

BA_Pete
Super User
Super User

Hi @Eddistoff ,

 

Add this as a new custom column to restate the approval date in the correct format:

let
    __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
in
    if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
    then Text.Combine(
        {
            Text.Middle([Date of approval], 3, 2),
            Text.Start([Date of approval], 2),
            Text.End([Date of approval], 4)
        },
        "/"
    )
    else [Date of approval]

 

From here you can change the data type of your new column to Date Type and perform your calculation.

 

Example as working query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBC8IwDIX/S88bTdLMrWd33Vlk7CTizUHx/+Mrri4HpSuENo98fUnm2ZF66ryQiGscI5DtwnlN6X574TWO7TS1Vxy3NMCip6FU5UDGVIRLWp8P3CDA7ZAaKGQIv2gNYk9SanRrUA41+B2j27BY8wLUl5pTXggZ4b9XMF79x6u6DA5mjCF76ZG5hM3KYl6+GOE3trwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of registration" = _t, Product = _t, #"Date of approval" = _t, #"Comments " = _t, #"Format entered" = _t]),
    chgRegDateType = Table.TransformColumnTypes(Source,{{"Date of registration", type date}}),
    addDtApproval =
    Table.AddColumn(
        chgRegDateType,
        "dtApproval", each
        let
            __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
        in
        if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
        then Text.Combine(
            {
                Text.Middle([Date of approval], 3, 2),
                Text.Start([Date of approval], 2),
                Text.End([Date of approval], 4)
            },
            "/"
        )
        else [Date of approval]
    )
in
    addDtApproval

 

Working query output:

BA_Pete_0-1673275424704.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors