Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago
Solved

Adding alias to table column names

Morning, I am using table in power bi and referencing it as so I wouldnt need to create multiple query request. There is 'Rates' columns form 1-20 and I am grouping them together to have number of h...
Need Help
  • KNP's avatar
    KNP
    2 years ago

    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

     

KNP's avatar
KNP
Super User
2 years ago

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.

 

 

 

  • KNP's avatar
    KNP
    Super User
    2 years ago

    Justas4478 ,

     

    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"
    • Justas4478's avatar
      Justas4478
      Post Prodigy
      2 years ago

      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}}

       

      • Justas4478's avatar
        Justas4478
        Post Prodigy
        2 years ago

        I had this code that looks like it could work, would you be able to check if ti is ok?
        #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Minutes", each Duration.FromText([Value]) / #duration(0, 0, 1, 0)),
        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Minutes", type number}}),