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.

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    2 years ago

    123abc Hi, I tried the measure you have suggested in the comments and I got this error.

     

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

      I apologize for the confusion. It seems there might be an issue with the syntax or structure of the measures. In Power BI, you create measures using DAX (Data Analysis Expressions), and the syntax might vary depending on your specific use case.

      Let me provide you with an example of how you can create measures for the sum of each rate column:

       

      RateAlias01 = SUM('YourTable'[Rate01]),
      RateAlias02 = SUM('YourTable'[Rate02]),
      // Repeat the above line for Rate03 to Rate20

       

      In this example, replace 'YourTable' with the actual name of your table where the rate columns are located. Also, ensure that the column names (Rate01, Rate02, etc.) match your actual column names.

      To create measures in Power BI:

      1. Go to the "Model" view.
      2. Select the table where your data is located.
      3. Click on "New Measure" in the ribbon.
      4. Enter the DAX expression for the measure based on the examples provided.

      If you encounter any issues, please share more details about your data model, and I'll do my best to assist you further.

       

      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.

      • Justas4478's avatar
        Justas4478
        Post Prodigy
        2 years ago

        123abc It does not complain when I use 'RateAlias' measures instead of columns, but it is still complaining about [Per_sBadgeNo] column being in the DAX query and gives still same error as before.