Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have 2 integer date fields (YYYYMMDD) in my fact table with several million records, and I work in direct query mode.
I have trouble doing the nescessary calculations (f.eks. number of days between the two dates) because PBI will not allow me to do the nescessary field transformations or adding of columns.
Importing the table is not an option, it will take too much time. Any suggestions or workarounds for this issue?
Thanks,
Hi adegrno,
If you are using SQL server direct query, you coiuld try to achieve this in M code like below
= Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for yr reply Zoe, is there something that should be written in front of the "="?
= Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))
the "dbo_test" I replace with the table name in which my integer date columns are. When I write the table name it will answer the table name is not correctly spelled. I am 100% sure it is.
the ST and ET I have no problems with.
I am connected to a SQL server
Regards,
Hello again
ok so I checked the syntax and it works just fine as long as I am in query editor mode.
When I wish to close and use the results in the model PBI will state that this step contains a query that is not supported in direct query mode.
So, I see the Custom column, the values are correct in the column (meaning your M does the trick) but Power bi will not let me use it.
Any suggestions?
Regards,
Albert
Hi adegrno.
I forget this is direct query mode, you could try to achieve this in sql query like below
select name, amount, convert(datetime,convert(varchar(20),st)) as st, convert(datetime,convert(varchar(20),et)) as et from test
You could convert this in sql, then use converted data in powerbi.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |