Forum Discussion
Multiple date formats in column
Leave everything, in a custom column put this. That's it.
= Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date])If you want to convert this column to a date, select column - Transform tab - Detect data type
I'm somewhat confused.
The order that I add the column is:
1) Transform
2) Add Column - Customer Column
3) I paste your line of code in there - get the below...(Then you mention I should replace Source with my previous step - I dont understand.)
4. If I expand the Custom column, I get this...
Then I select the Date Effective field only and I get an Error...
All the Steps I have taken up to here is
I dont know where I am going wrong
Thanks
Do following -
1. Paste your sample data as table here. Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
2. Go to advanced editor, copy the code, in this text box press </> above, paste the code
Press Reply.
Ok let me try..here is sample data from my Excel file
22-Jun-21 08-Nov-21 08-Nov-21 08-Nov-21 08-Nov-21 08-Nov-21 March '22 March '22 March '22 March '22 Here is the exact code I am using
Table.AddColumn(Source, "Date Effective", each Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date]), type date)My column name in the Excel file is [Live date] .
Hope this helps
See the code below where I have changed Text.Contains([Live date],"'") to Text.Contains(Text.From([Live date]),"'")
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Result = Table.AddColumn(Source, "Date Effective", each Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date])) in Result1. your first statement -
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],1. do I paste it into the Applied step Source?
2. The next part - I assume that is the new calculated column - please explain the change?
Thankful
Leave everything, in a custom column put this. That's it.
= Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date])If you want to convert this column to a date, select column - Transform tab - Detect data type
Stunning - it works. Please may you explain what made it work?
Appreciate your assistance.