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

     

123abc's avatar
123abc
Community Champion
2 years ago

In Power BI, you can create calculated columns or measures to create aliases or perform calculations on your data. In your case, you mentioned that you want to group the "Rates" columns together and create a sum for each day and person. Instead of using aliases, you can achieve this by creating a new column or measure.

Here is an example of how you can create a measure to sum the rates for each day and person:

  1. Open the Power BI Desktop file.

  2. In the Fields pane, go to the table where your data is located.

  3. Click on "New Measure" from the Modeling tab in the ribbon.

  4. Use the following DAX formula to create a measure that sums the rates:

DayTotal =
CALCULATE(
SUMX(
VALUES('YourTableName'[AttDate], 'YourTableName'[Per_sBadgeNo]),
'YourTableName'[Rate01] + 'YourTableName'[Rate02] + 'YourTableName'[Rate03] + 'YourTableName'[Rate04] + 'YourTableName'[Rate05] +
'YourTableName'[Rate06] + 'YourTableName'[Rate07] + 'YourTableName'[Rate08] + 'YourTableName'[Rate09] + 'YourTableName'[Rate10] +
'YourTableName'[Rate11] + 'YourTableName'[Rate12] + 'YourTableName'[Rate13] + 'YourTableName'[Rate14] + 'YourTableName'[Rate15] +
'YourTableName'[Rate16] + 'YourTableName'[Rate17] + 'YourTableName'[Rate18] + 'YourTableName'[Rate19] + 'YourTableName'[Rate20]
)
)

 

Replace 'YourTableName' with the actual name of your table.

  1. Press Enter to create the measure.

Now, you can use this "DayTotal" measure in your visuals to display the sum of rates for each day and person.

If you prefer creating a new column instead, you can modify the formula accordingly and create a calculated column instead of a measure. Keep in mind that calculated columns store the data in the table, while measures are calculated on the fly in visuals.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.