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

     

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

 

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

       

      • Justas4478's avatar
        Justas4478
        Post Prodigy
        2 years ago

        KNP Yea this works now.
        Thank you for the help.