cancel
Showing results for
Did you mean:
See all
##### Top groups
Explore all groups

# How to getting card creation date in Power BI

Emiliana Candida
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 15, 2020

I connected Trello to Power BI and I'm having trouble getting the creation date of my cards. It only provides the last activity and due date.

Eugene Kelly
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 6, 2023

Figured it out.  Not the cleanest but gets the job done.

#1) In Power Query Editor -> New Source -> Blank Query

#2) Paste the following in.

let
HexToDecimal = (input) =>

let

first = Text.Middle(input, 0, 1),
first_decimal = if first = "A" then 10 else if first = "B" then 11 else if first = "C" then 12 else if first = "D" then 13 else if first = "E" then 14 else if first = "F" then 15 else Number.FromText(first),
total1=first_decimal*(16*16*16*16*16*16*16),

second = Text.Middle(input, 1, 1),
second_decimal = if second = "A" then 10 else if second = "B" then 11 else if second = "C" then 12 else if second = "D" then 13 else if second = "E" then 14 else if second = "F" then 15 else Number.FromText(second),
total2=(second_decimal*(16*16*16*16*16*16)),

third = Text.Middle(input, 2, 1),
third_decimal = if third = "A" then 10 else if third = "B" then 11 else if third = "C" then 12 else if third = "D" then 13 else if third = "E" then 14 else if third = "F" then 15 else Number.FromText(third),
total3=(third_decimal*(16*16*16*16*16)),

fourth = Text.Middle(input, 3, 1),
fourth_decimal = if fourth = "A" then 10 else if fourth = "B" then 11 else if fourth = "C" then 12 else if fourth = "D" then 13 else if fourth = "E" then 14 else if fourth = "F" then 15 else Number.FromText(fourth),
total4=(fourth_decimal*(16*16*16*16)),

fifth = Text.Middle(input, 4, 1),
fifth_decimal = if fifth = "A" then 10 else if fifth = "B" then 11 else if fifth = "C" then 12 else if fifth = "D" then 13 else if fifth = "E" then 14 else if fifth = "F" then 15 else Number.FromText(fifth),
total5=(fifth_decimal*(16*16*16)),

sixth = Text.Middle(input, 5, 1),
sixth_decimal = if sixth = "A" then 10 else if sixth = "B" then 11 else if sixth = "C" then 12 else if sixth = "D" then 13 else if sixth = "E" then 14 else if sixth = "F" then 15 else Number.FromText(sixth),
total6=(sixth_decimal*(16*16)),

seventh = Text.Middle(input, 6, 1),
seventh_decimal = if seventh = "A" then 10 else if seventh = "B" then 11 else if seventh = "C" then 12 else if seventh = "D" then 13 else if seventh = "E" then 14 else if seventh = "F" then 15 else Number.FromText(seventh),
total7=(seventh_decimal*(16)),

eighth = Text.Middle(input, 7, 1),
eighth_decimal = if eighth = "A" then 10 else if eighth = "B" then 11 else if eighth = "C" then 12 else if eighth = "D" then 13 else if eighth = "E" then 14 else if eighth = "F" then 15 else Number.FromText(eighth),
total8=(eighth_decimal*1),

final_total=total1+total2+total3+total4+total5+total6+total7+total8,

//Convert Time
time1 = #duration(0,0,0,final_total)+#datetime(1970,1,1,0,0,0)

in

time1
in
HexToDecimal

#3) Rename your blank query in the left side bar to HexToDecimal.  You can do this just by double clicking on it.

#4) Go to your card table. Click on the ID and then click Add Column -> Extract and select 8.  Rename this new column to Hex Timestamp.

#5) Transform this column to all uppercase.

#6) Go to your card table and click Add Column -> Custom Column.  Paste in the following:

HexToDecimal([#"Hex Timestamp"])

#7) Rename this column to Created

DONE.

Eugene Kelly
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
August 29, 2023

I am also interested in a solution to this.