How to getting card creation date in Power BI

Question

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 · Answer

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 · Answer

I am also interested in a solution to this.