Combining columns into one

Power Query
1

Hi,

This is my first post and a newbie in power BI. I apologize in advance if in case I’ve posted incorrectly or didn’t provide enough details.

I need help on how to combine different columns into the query.

The columns I have are …

Country Date
Afghanistan 08/03/2021

and I want to combine it in one column which will have country+month number+year which looks like this Afghanistan12017. Can you guide me on how to do this?

Thanks again :slight_smile:

2

Hi @BM_Girlracer

You can use below formula in custom column.

[Country] & " " & Number.ToText(Date.Month([Date])) & Number.ToText(Date.Year([Date]))

image
image877×385 14.3 KB

3 Likes
3

Hi @BM_Girlracer

Welcome to the forum.

As you are new to Power BI, I just want to share we’ve a wonderful option in Power Query Column from Examples. You can try that…

Combining columns into one
Combining columns into one1894×828 938 KB

4

@BM_Girlracer,

Just one question about your requirement - is it sufficient to have single digit months occupy one space and double digit months two spaces, or would you always want the month number to occupy the two spaces after the couhntry? (e.g., Afghanistan82021 vs. Afghanistan082021). The latter requires just one slight tweak, but I often find in these merged fields having each component consistently occupy the same space in the string is very useful.

  • Brian
5

Thanks MK3010! I tried it and it works. But I’ve noticed that there are blank Countries in my dataset, what would be the formula if I want the custom column to show blank countries as Blank+MonthNum+Year?

6

Thanks Rajesh, this is useful info :slight_smile:

7

Hi Brian,

It’s not necessary for now but might be in the future. Can you please share how to do it just in case?

8

Hi @BM_Girlracer,

You can use the Text.PadStart M function for that, paste this into a new blank query to see it in action.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxLz0jMyywuScxT0lGy0DXWNTIwMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Date = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Date", type date}}),
    AddCustom = Table.AddColumn(ChType, "Custom", each Text.Combine( {[Country], " ", Text.PadStart( Text.From( Date.Month( [Date] )), 2, "0"), Text.From( Date.Year( [Date] )) }), type text)
in
    AddCustom

.
image

I hope this is helpful

9

Hi @BM_Girlracer,

If you have blank then also it will work but if you need extra blank whenever you find a blank country then you can use “if” condition as below

if [Country]= null then " " & Number.ToText(Date.Month([Date])) & Number.ToText(Date.Year([Date])) else [Country] & " " & Number.ToText(Date.Month([Date])) & Number.ToText(Date.Year([Date]))

Thanks
Mukesh

10

It’s great to know that you are making progress with your query @BM_Girlracer. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

11

Thanks again! This solves my problem

12

Thanks Melissa, this is very useful :slight_smile: