MERGE QUERIES

[himaan_she]

Hi,

I was trying to merge three columns called Day, Month and Year in Power Query to form a new column called "Date" and then changing the type of the "Date" column to date type, but getting errors for some rows. How to encounter this? Can anyone please help me out?

[v-karpurapud]

Hi @himaan_she 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @himaan_she 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rohit1991]

Hi @himaan_she 

To return only rows that exist in both tables, use Merge Queries in Power Query and select the column you want to match on. Choose the Inner Join option it will keep only the rows that match in both tables.

[v-karpurapud]

Hi @himaan_she 

Could you please confirm if your query have been resolved the solution provided by @SundarRaj, @Suraj_K and @p45cal ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[mussaenda]

Hi @himaan_she ,

What is the error you are enconutering? Usually the error is caused by different data types that you are trying to merge into one column.

[p45cal]

Perhaps link to your file; check the data types of the Day/Month/Year columns in those rows you get errors, also check you're not trying to make impossible dates (eg. 31th April) and watch out for spaces and the like in those columns.

Separately, a one-liner to do this:

addMergedDate = Table.AddColumn(PreviousStep, "Date", each #date([Year],[Month],[Day]),type date)

[Suraj_K]

First, check your data types and formats. If you are still getting errors, can you please share the error screen briefly if there is a specific error?

[SundarRaj · Accepted solution]

Hi @himaan_she 
Have a look at this

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Day", type text}, {"Month", type text}, {"Year", type text}}, "en-IN"),{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
#"Changed Type1"