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.

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

        I see, it seems that the issue may be related to the presence of a column in the DAX query that's causing the error. If you are encountering issues with a specific column, you might want to check how that column is being used in your DAX measures and if it is causing any conflicts.

        Here are a few suggestions to troubleshoot and resolve the issue:

        1. Check for Ambiguities:

          • Ensure that there are no ambiguous references in your DAX measures. For example, if there are multiple tables with the same column name, Power BI might have difficulty resolving which column to use.

        2. Qualify Column Names:

          • Fully qualify the column names in your DAX measures to avoid any ambiguity. For example:

        Rate01_Alias = SUM('YourTableName'[Rate01])

         

        1. Check Dependencies:

          • Examine other measures or calculated columns that might be using the problematic column. Ensure that there are no circular dependencies or conflicting references.

        2. Review Advanced Editor:

          • If you are comfortable with the Power Query M language, you can review the Advanced Editor and inspect the query code. Look for any references to the problematic column and ensure they are correct.

        If the issue persists, it might be helpful to see more of your DAX code or understand the specific steps where the error is occurring. If you can provide more details or share the relevant part of your DAX code, 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.