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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ET2018
Frequent Visitor

Append queries problem

I tried to append 2 tables with identical columns. 

However, the appended queries created a new date column.

 

Seems date column of 2 tables cannot be appended, how to fix this issue?

 

 

圖片2.png圖片1.png

 

 

 

1 ACCEPTED SOLUTION

The queries reference local files, so I can't try resolving this in the Query Editor,

 

But if you create a calculated table based on your two source tables

 

 

Union Table = UNION('Data_ISS ECP','Data_Lo''s WAS')

That looks like it works just fine to me

 

 

Edit: I also see the following in the applied steps (again, can't see the data to confirm) but it looks like it might be part of the issue.

 

For the "Changed Type" step in both tables, the date column is handled differently. For ISS ECP you have 

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Month", Int64.Type}, {"Date#(lf)", type date}, ...

And for Lo's WAS you have

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Month", Int64.Type}, {"Date", type date}, 

It looks like you tried to modify the type for Lo's WAS but not ISS ECP. and Lo's WAS has a slightly different date name (Date#(lf) instead of Date). Pretty sure the #(lf) stands for 'line feed' and is one of the hidden characters I was referring to in the last post.

View solution in original post

11 REPLIES 11
prihana
Helper V
Helper V

Hi,

 

Probably two different date formats. I tried the same and got the below result. 

 

Capture.PNG

johnmu
Helper I
Helper I

Could it be that the two columns are different formats?

 

They both look like dates, but maybe one is mm/dd/yyyy and the other dd/mm/yyyy

ET2018
Frequent Visitor

I have checked the data format which is exactly the same. 

I even copy columns from 1 table to another to ensure the format and wordings are the same, still the tables cannot be appened correctly

Show me the source tables

I have checked the header also. 

Amended the spacing and still failed.

 

https://www.dropbox.com/s/2e9unui876v14p7/test.pbix?dl=0

The queries reference local files, so I can't try resolving this in the Query Editor,

 

But if you create a calculated table based on your two source tables

 

 

Union Table = UNION('Data_ISS ECP','Data_Lo''s WAS')

That looks like it works just fine to me

 

 

Edit: I also see the following in the applied steps (again, can't see the data to confirm) but it looks like it might be part of the issue.

 

For the "Changed Type" step in both tables, the date column is handled differently. For ISS ECP you have 

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Month", Int64.Type}, {"Date#(lf)", type date}, ...

And for Lo's WAS you have

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Month", Int64.Type}, {"Date", type date}, 

It looks like you tried to modify the type for Lo's WAS but not ISS ECP. and Lo's WAS has a slightly different date name (Date#(lf) instead of Date). Pretty sure the #(lf) stands for 'line feed' and is one of the hidden characters I was referring to in the last post.

Thanks for the solution, it worked. But I can't view this union table in the power query editor to make few changes. How can I make this table viewable in the editor ?

ET2018
Frequent Visitor

thanks.

 

As I have more than 2 tables so Union can't help.

 

I changed the header again in edit query, working now.

Glad to hear it. FYI, you can use UNION with more than two tables. Still if you can resolve this at the Query Editor I think that's better.

ET2018
Frequent Visitor

OMG.

 

Actually, I never tried UNION for 2+ tables, as the description said 2 tables. 

Maybe extra characters in the header Names?

 

Eg:

"Date " and "Date"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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