Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
RahulPBI
Helper I
Helper I

Concatenate Text with Week Number column

Hi Experts

I am trying to concatenate text with the a week number column but I am getting an error. I've tried two diffrent options and none work

= &"CONCATENATE("WK-",Calendar[Week])

= &"WK-"&[Week]

 

Can someone please help me

Regards

Rahul

2 ACCEPTED SOLUTIONS
AlB
Community Champion
Community Champion

Hi @RahulPBI 

Is what you are showing the code for a custom column in the query editor?? If so, your second option is close:

    = "WK-"&[Week]

assuming of course that "Week" is the name of the column you already have and it has the week number

If this is not it share a sample of your data. This is very important to get you question answered

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

AlB
Community Champion
Community Champion

@RahulPBI 

Just update the last step. The error message is saying quite clearly what the problem is: "We cannot apply operator & to types Text and Number" [Week] is of type numer so you have to convert it to text before applying the & operator:

 

 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"& Text.From( [Week] ) )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

View solution in original post

4 REPLIES 4
AlB
Community Champion
Community Champion

Hi @RahulPBI 

Is what you are showing the code for a custom column in the query editor?? If so, your second option is close:

    = "WK-"&[Week]

assuming of course that "Week" is the name of the column you already have and it has the week number

If this is not it share a sample of your data. This is very important to get you question answered

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB 

Thanks for your reply

 

Please see below what I have done so far

 

let

EndFiscalYearMonth = 6, //set this as the last month number of your fiscal year : June = 6, July =7 etc


StartDate= #date(2019, 1, 1), // Change start date #date(yyyy,m,d)
EndDate = #date(2020, 12, 31), // Could change to DateTime.LocalNow() if you want to always show up to the current date


/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the //
*/


//TableName = Sales,
//DateColumnName = "Invoice Date",
//StartDate = Record.Field ( Table.Min(TableName,DateColumnName) ,DateColumnName),
//EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),



DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),

#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Year",{{"Calendar Year", Int64.Type}}),
#"Inserted Month Number" = Table.AddColumn(#"Changed Type1", "Month Number", each Date.Month([Date]), type number),
#"Changed Type2" = Table.TransformColumnTypes(#"Inserted Month Number",{{"Month Number", Int64.Type}}),
#"Long Month Name" = Table.AddColumn(#"Changed Type2", "Month Long", each Date.MonthName([Date]), type text),
#"Inserted Week of Year" = Table.AddColumn(#"Long Month Name", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Week of Year",{{"Week of Year", "Week"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"&[Week])
in
#"Added Custom"

 

Regards

Rahul

AlB
Community Champion
Community Champion

@RahulPBI 

Just update the last step. The error message is saying quite clearly what the problem is: "We cannot apply operator & to types Text and Number" [Week] is of type numer so you have to convert it to text before applying the & operator:

 

 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"& Text.From( [Week] ) )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Thank you 

Very much appreciated

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.