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.
Struggling with Diret query and making a custom grouping "date" field.
I turned the date (non-contiguous) into a Year_Month field but I need the leading 0 in the month so it sorts properly. I can't figure out FORMAT() and suspect even in this case Direct Query says No to that.
My formula:
Please, thank you and I give KUDOS and mark as Solved!
Solved! Go to Solution.
Hi @Anonymous ,
You can try to create a custom column in power query like this:
=Table.AddColumn(#"Removed Other Columns", "Custom", each if
Date.Month([FullDateAlternateKey]) < 10 then
Text.From(Date.Year([FullDateAlternateKey])) & "0" &
Text.From(Date.Month([FullDateAlternateKey]))
else
Text.From(Date.Year([FullDateAlternateKey])) &
Text.From(Date.Month([FullDateAlternateKey])))
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
This expression gets your desired column with leading zeros.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
You can try to create a custom column in power query like this:
=Table.AddColumn(#"Removed Other Columns", "Custom", each if
Date.Month([FullDateAlternateKey]) < 10 then
Text.From(Date.Year([FullDateAlternateKey])) & "0" &
Text.From(Date.Month([FullDateAlternateKey]))
else
Text.From(Date.Year([FullDateAlternateKey])) &
Text.From(Date.Month([FullDateAlternateKey])))
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Solved it. This seems like it would be a drag on the performance because I had to make a YEAR and MONTH column for the date field, then use IF() to bring them together.
A more eloquent solution would be appreciated.
Cheers!
MY SOLUTION
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |