Hi @Justas4478,
I think I figured out what might be going on.
When you shared the sample data, it looks like this...
I wonder if it was just Excel that added that date portion?
If that's the case I'll need to edit the code. It would be good to have a sample file with the correct formats.
------
I've done some checks and I think you should just be able to delete that one step.
Try this code.
let
Source = Table1,
Unpivot = Table.UnpivotOtherColumns(Source, {"Per_sBadgeNo", "AttDate"}, "Attribute", "Value"),
CT1 = Table.TransformColumnTypes(
Unpivot,
{
{"Per_sBadgeNo", Int64.Type},
{"AttDate", type date},
{"Attribute", type text},
{"Value", type duration}
}
),
GR1 = Table.Group(
CT1,
{"Per_sBadgeNo", "AttDate"},
{{"Duration", each List.Sum([Value]), type nullable duration}}
),
AddTotalMinutes = Table.AddColumn(
GR1,
"Total Minutes",
each Duration.TotalMinutes([Duration]),
type number
),
AddTotalHours = Table.AddColumn(
AddTotalMinutes,
"Total Hours",
each Duration.TotalHours([Duration]),
type number
)
in
AddTotalHours
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
With regards to grouping the data by day and person, I'm trying to understand the need for duplicating the columns before unpivoting the new columns. Why not just unpivot the original?
The below code is based on referencing the table (Table1) in the file you shared.
let
Source = Table1,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
Source,
{"Per_sBadgeNo", "AttDate"},
"Attribute",
"Value"
),
#"Changed Type" = Table.TransformColumnTypes(
#"Unpivoted Other Columns",
{
{"Per_sBadgeNo", Int64.Type},
{"AttDate", type date},
{"Attribute", type text},
{"Value", type duration}
}
),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Per_sBadgeNo", "AttDate"},
{{"Duration", each List.Sum([Value]), type nullable duration}}
),
#"Inserted Total Minutes" = Table.AddColumn(
#"Grouped Rows",
"Total Minutes",
each Duration.TotalMinutes([Duration]),
type number
),
#"Inserted Total Hours" = Table.AddColumn(
#"Inserted Total Minutes",
"Total Hours",
each Duration.TotalHours([Duration]),
type number
)
in
#"Inserted Total Hours"
I realise there could be many reasons why you needed to do it that way but just thought I'd float the idea in case it helps.
As far as the renaming/aliasing of columns goes, can you give examples of what they are and why they would need to be renamed to achieve the result. I don't think I understand this part fully.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Just a note, I did the above in Excel.
For Power BI you'd likely need the extra step to get the duration.
let
Source = Table1,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
Source,
{"Per_sBadgeNo", "AttDate"},
"Attribute",
"Value"
),
#"Extracted Text Between Delimiters" = Table.TransformColumns(
#"Unpivoted Other Columns",
{{"Value", each Text.BetweenDelimiters(Text.From(_, "en-NZ"), " ", " "), type text}}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Extracted Text Between Delimiters",
{
{"Per_sBadgeNo", Int64.Type},
{"AttDate", type date},
{"Attribute", type text},
{"Value", type duration}
}
),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Per_sBadgeNo", "AttDate"},
{{"Duration", each List.Sum([Value]), type nullable duration}}
),
#"Inserted Total Minutes" = Table.AddColumn(
#"Grouped Rows",
"Total Minutes",
each Duration.TotalMinutes([Duration]),
type number
),
#"Inserted Total Hours" = Table.AddColumn(
#"Inserted Total Minutes",
"Total Hours",
each Duration.TotalHours([Duration]),
type number
)
in
#"Inserted Total Hours"| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @KNP I tried to use code that you provided it worked for most of the steps, but for this step it just changed all values to empty. I dont know what went wrong.
{{"Value", each Text.BetweenDelimiters(Text.From(_, "en-NZ"), " ", " "), type text}}
Hi @Justas4478,
The only obvious thing that I overlooked when I pasted the code is the culture ("en-NZ") change this to whatever is appropriate to you (en-US?) and that may solve the issue.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @KNP I tried changing to US and it still makes all values empty.
These are the format values are in.
Hi @Justas4478,
I think I figured out what might be going on.
When you shared the sample data, it looks like this...
I wonder if it was just Excel that added that date portion?
If that's the case I'll need to edit the code. It would be good to have a sample file with the correct formats.
------
I've done some checks and I think you should just be able to delete that one step.
Try this code.
let
Source = Table1,
Unpivot = Table.UnpivotOtherColumns(Source, {"Per_sBadgeNo", "AttDate"}, "Attribute", "Value"),
CT1 = Table.TransformColumnTypes(
Unpivot,
{
{"Per_sBadgeNo", Int64.Type},
{"AttDate", type date},
{"Attribute", type text},
{"Value", type duration}
}
),
GR1 = Table.Group(
CT1,
{"Per_sBadgeNo", "AttDate"},
{{"Duration", each List.Sum([Value]), type nullable duration}}
),
AddTotalMinutes = Table.AddColumn(
GR1,
"Total Minutes",
each Duration.TotalMinutes([Duration]),
type number
),
AddTotalHours = Table.AddColumn(
AddTotalMinutes,
"Total Hours",
each Duration.TotalHours([Duration]),
type number
)
in
AddTotalHours
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
That code doesn't work for me so I'm not sure. Even if I convert the Value column to text first it fails.
I'll wait to hear back from my other response to see if the problem is solved.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hey @Justas4478,
Is there any way you can share some made-up data that matches the format or mask the sensitive info?
It'll be easier to give you a useful answer with some data. I'm not fully understanding the purpose for duplicating the columns.
I promise I won't even use ChatGPT. 😂
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
