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

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

    • KNP's avatar
      KNP
      Super User
      2 years ago

      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.

       

  • KNP's avatar
    KNP
    Super User
    2 years ago

    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.

     

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      2 years ago

      Hi KNP I tried changing to US and it still makes all values empty.
      These are the format values are in.

       

      • KNP's avatar
        KNP
        Super User
        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