Forum Discussion
MERGE QUERIES
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?
Hi himaan_she
Have a look at thisHere'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"
8 Replies
Hi himaan_she
Have a look at thisHere'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"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?
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)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.
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
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.
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.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.
