Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good day - my first post on the Forum! :)-
I'm reading from an Excel file, and the format of the date column is either 2021/11/08, or March '22 (in this case, it can default to 2022/11/01).
I suppose I could manually standardize/update it, as it is not a large file, but I would like to get to know it in code.
All help appreciated - please keep in mind I'm a beginner.
Solved! Go to Solution.
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
Have you tried selecting the column and clicking Parse Date under the Transform-Date option? Or a custom column using Date.FromText?
--Nate
Since you are beginner, I will suggest that you insert a new custom column and put following formula where [Date] needs to be replaced with your column
= Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date])
If you want to do in place column replacement, then insert following statement where Source needs to be replaced with your previous step.
= Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ3NNQ3sFCK1YlW8k0sSs5QiCk1MDAyNzJSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
Custom1 = Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})
in
Custom1
Hello, thank you very much for your response.
1. I have tried the first option, but it gives me an error - would you mind if I send you the file here? I'm not sure how to attach it here.
2. How do I create a blank query (3rd point in your reply)?
So much appreciated. Thanks
1. Make sure [Date] is replaced with your column name.
2. Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.
3. For Blank query - Right click on left side pane - New Query - Blank Query
1. My Field name is 'Live date' so my new column looks like this..
Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date])
2. The [Live date] is currently a text field.
3. The New field is also a text field - the "replace" part in the script above works...(although it is still Text format..
4. However the dates that were (correct) dates before, produce the error.
I suspect it might be a Data type error?
Can you click on one of the errors and post the error here? To have the column in date format, use below step (Replace Source with your previous step)
= Table.AddColumn(Source, "Custom", each Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date]), type date)
OR you can select the Custom column - Transform menu - 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-...
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
Result
1. 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.
I'm so sorry - I am really stupid with this.
1. The error message that the values cannot be converted to text - so your thinking that it needs to be converted to date is correct.
2. In your suggestion - it starts with Table.Addcolumn....why is that - I still have a query, not a table...
thanks
Unfortunately my organization prevents using any of these services
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |