Forum Discussion

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 hours joined for same day for same person.

The problem is that in table that I am referencing these columns have been renamed for users to match names on original system.

For me to do my work I would have to rename them back, do the work and rename again all 20 columns.
Is there some sort of alias option that could be used maybe?

This is the query that has all the steps, the query is not fully finished but at the point where it has information that I need and all is left to clean up the table.

Thanks

let
Source = #"Database Query",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AttDate", type date}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type",{"Per_sBadgeNo", "Type", "Status", "Status Desc", "AttDate", "Per_sFName", "Per_sLName", "Per_sUnionDetails", "Per_iWklySchdId", "WSCode", "WSDescription", "Per_iCmp_id", "comp_sName", "Per_iDept_id", "dept_sCode", "dept_sDesc", "Per_iGroup_id", "group_sCode", "group_sDesc", "AbsId", "refcode_sCode", "refcode_sDesc", "absCate_SDesc", "DsId", "Code", "Description", "Per_sContractHours", "Per_E1CLeaveDate", "InSwipe01", "OutSwipe02", "InSwipe03", "OutSwipe04", "Rate01", "Rate02", "Rate03", "Rate04", "Rate05", "Rate06", "Rate07", "Rate08", "Rate09", "Rate10", "Rate11", "Rate12", "Rate13", "Rate14", "Rate15", "Rate16", "Rate17", "Rate18", "Rate19", "Rate20", "ContributetoOvertime", "Override_Rate", "ShiftAllowance1", "ShiftAllowance2", "ShiftAllowance3", "ShiftAllowance4", "ShiftAllowance5"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns2", "Rate01", "Rate01 - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Rate02", "Rate02 - Copy"),
#"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Rate03", "Rate03 - Copy"),
#"Duplicated Column3" = Table.DuplicateColumn(#"Duplicated Column2", "Rate04", "Rate04 - Copy"),
#"Duplicated Column4" = Table.DuplicateColumn(#"Duplicated Column3", "Rate05", "Rate05 - Copy"),
#"Duplicated Column5" = Table.DuplicateColumn(#"Duplicated Column4", "Rate06", "Rate06 - Copy"),
#"Duplicated Column6" = Table.DuplicateColumn(#"Duplicated Column5", "Rate07", "Rate07 - Copy"),
#"Duplicated Column7" = Table.DuplicateColumn(#"Duplicated Column6", "Rate08", "Rate08 - Copy"),
#"Duplicated Column8" = Table.DuplicateColumn(#"Duplicated Column7", "Rate09", "Rate09 - Copy"),
#"Duplicated Column9" = Table.DuplicateColumn(#"Duplicated Column8", "Rate10", "Rate10 - Copy"),
#"Duplicated Column10" = Table.DuplicateColumn(#"Duplicated Column9", "Rate11", "Rate11 - Copy"),
#"Duplicated Column11" = Table.DuplicateColumn(#"Duplicated Column10", "Rate12", "Rate12 - Copy"),
#"Duplicated Column12" = Table.DuplicateColumn(#"Duplicated Column11", "Rate13", "Rate13 - Copy"),
#"Duplicated Column13" = Table.DuplicateColumn(#"Duplicated Column12", "Rate14", "Rate14 - Copy"),
#"Duplicated Column14" = Table.DuplicateColumn(#"Duplicated Column13", "Rate15", "Rate15 - Copy"),
#"Duplicated Column15" = Table.DuplicateColumn(#"Duplicated Column14", "Rate16", "Rate16 - Copy"),
#"Duplicated Column16" = Table.DuplicateColumn(#"Duplicated Column15", "Rate17", "Rate17 - Copy"),
#"Duplicated Column17" = Table.DuplicateColumn(#"Duplicated Column16", "Rate18", "Rate18 - Copy"),
#"Duplicated Column18" = Table.DuplicateColumn(#"Duplicated Column17", "Rate19", "Rate19 - Copy"),
#"Duplicated Column19" = Table.DuplicateColumn(#"Duplicated Column18", "Rate20", "Rate20 - Copy"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Duplicated Column19", {"Per_sBadgeNo", "Type", "Status", "Status Desc", "AttDate", "Per_sFName", "Per_sLName", "Per_sUnionDetails", "Per_iWklySchdId", "WSCode", "WSDescription", "Per_iCmp_id", "comp_sName", "Per_iDept_id", "dept_sCode", "dept_sDesc", "Per_iGroup_id", "group_sCode", "group_sDesc", "AbsId", "refcode_sCode", "refcode_sDesc", "absCate_SDesc", "DsId", "Code", "Description", "Per_sContractHours", "Per_E1CLeaveDate", "InSwipe01", "OutSwipe02", "InSwipe03", "OutSwipe04", "Rate01", "Rate02", "Rate03", "Rate04", "Rate05", "Rate06", "Rate07", "Rate08", "Rate09", "Rate10", "Rate11", "Rate12", "Rate13", "Rate14", "Rate15", "Rate16", "Rate17", "Rate18", "Rate19", "Rate20", "ContributetoOvertime", "Override_Rate", "ShiftAllowance1", "ShiftAllowance2", "ShiftAllowance3", "ShiftAllowance4", "ShiftAllowance5"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "RateType"}, {"Value", "Rate"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Rate] <> "")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Minutes", each Duration.FromText([Rate]) / #duration(0, 0, 1, 0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rate"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Per_sBadgeNo", "Type", "Status", "Status Desc", "Per_sFName", "Per_sLName", "Per_sUnionDetails", "Per_iWklySchdId", "WSCode", "WSDescription", "Per_iCmp_id", "comp_sName", "Per_iDept_id", "dept_sCode", "dept_sDesc", "Per_iGroup_id", "group_sCode", "group_sDesc", "AbsId", "refcode_sCode", "refcode_sDesc", "absCate_SDesc", "DsId", "Code", "Description", "Per_sContractHours", "Per_E1CLeaveDate", "InSwipe01", "OutSwipe02", "InSwipe03", "OutSwipe04", "AttDate", "Rate01", "Rate02", "Rate03", "Rate04", "Rate05", "Rate06", "Rate07", "Rate08", "Rate09", "Rate10", "Rate11", "Rate12", "Rate13", "Rate14", "Rate15", "Rate16", "Rate17", "Rate18", "Rate19", "Rate20", "ContributetoOvertime", "Override_Rate", "ShiftAllowance1", "ShiftAllowance2", "ShiftAllowance3", "ShiftAllowance4", "ShiftAllowance5", "RateType", "Minutes"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"AttDate", "Per_sBadgeNo", "Type", "Status", "Status Desc", "Per_sFName", "Per_sLName", "Per_sUnionDetails", "Per_iWklySchdId", "WSCode", "WSDescription", "Per_iCmp_id", "comp_sName", "Per_iDept_id", "dept_sCode", "dept_sDesc", "Per_iGroup_id", "group_sCode", "group_sDesc", "AbsId", "refcode_sCode", "refcode_sDesc", "absCate_SDesc", "DsId", "Code", "Description", "Per_sContractHours", "Per_E1CLeaveDate", "InSwipe01", "OutSwipe02", "InSwipe03", "OutSwipe04", "Rate01", "Rate02", "Rate03", "Rate04", "Rate05", "Rate06", "Rate07", "Rate08", "Rate09", "Rate10", "Rate11", "Rate12", "Rate13", "Rate14", "Rate15", "Rate16", "Rate17", "Rate18", "Rate19", "Rate20", "ContributetoOvertime", "Override_Rate", "ShiftAllowance1", "ShiftAllowance2", "ShiftAllowance3", "ShiftAllowance4", "ShiftAllowance5"}, {{"Day Total", each List.Sum([Minutes]), type number}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Grouped Rows",{"Per_sBadgeNo", "Type", "Status", "Status Desc", "Per_sFName", "Per_sLName", "Per_sUnionDetails", "Per_iWklySchdId", "WSCode", "WSDescription", "Per_iCmp_id", "comp_sName", "Per_iDept_id", "dept_sCode", "dept_sDesc", "Per_iGroup_id", "group_sCode", "group_sDesc", "AbsId", "refcode_sCode", "refcode_sDesc", "absCate_SDesc", "DsId", "Code", "Description", "Per_sContractHours", "Per_E1CLeaveDate", "InSwipe01", "OutSwipe02", "InSwipe03", "OutSwipe04", "AttDate", "Rate01", "Rate02", "Rate03", "Rate04", "Rate05", "Rate06", "Rate07", "Rate08", "Rate09", "Rate10", "Rate11", "Rate12", "Rate13", "Rate14", "Rate15", "Rate16", "Rate17", "Rate18", "Rate19", "Rate20", "ContributetoOvertime", "Override_Rate", "ShiftAllowance1", "ShiftAllowance2", "ShiftAllowance3", "ShiftAllowance4", "ShiftAllowance5", "Day Total"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "Day Hours Total", each [Day Total]/60)
in
#"Added Custom1"

Need Help

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

20 Replies

KNP
Super User
2 years ago
Hey Justas4478,

Is there any way you can share some made-up data that matches the format or mask the sensitive info?

It'll be easier to give you a useful answer with some data. I'm not fully understanding the purpose for duplicating the columns.

I promise I won't even use ChatGPT. 😂
- Justas4478
  Post Prodigy
  2 years ago
  KNP I will try to put a data sample to share.
- 123abc
  Community Champion
  2 years ago
  Thank You Very Much Respected Sir. 😉
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:
Open the Power BI Desktop file.
In the Fields pane, go to the table where your data is located.
Click on "New Measure" from the Modeling tab in the ribbon.
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.
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
  Post Prodigy
  2 years ago
  123abc Hi, I tried the measure you have suggested in the comments and I got this error.
  - 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:
    Go to the "Model" view.
    Select the table where your data is located.
    Click on "New Measure" in the ribbon.
    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

Post Prodigy

2 years ago

123abc KNP Here is the sample file
https://we.tl/t-Ktv6ppaZAt

KNP

Super User

2 years ago

With regards to grouping the data by day and person, I'm trying to understand the need for duplicating the columns before unpivoting the new columns. Why not just unpivot the original?

The below code is based on referencing the table (Table1) in the file you shared.

let
  Source = Table1,
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    Source,
    {"Per_sBadgeNo", "AttDate"},
    "Attribute",
    "Value"
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Unpivoted Other Columns",
    {
      {"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"

I realise there could be many reasons why you needed to do it that way but just thought I'd float the idea in case it helps.

As far as the renaming/aliasing of columns goes, can you give examples of what they are and why they would need to be renamed to achieve the result. I don't think I understand this part fully.

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"

Forum Discussion

Adding alias to table column names

20 Replies

Related content

Adding columns

Adding a column

Renaming columns and adding columns

Alias on SummarizeColumns

Alias Static Column