cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanessafvg
Super User
Super User

column names and data separate - append data to columns?

not sure if im overcomplicating this but i have a huge amount of data  in compressed files in azure datalake, where i cannot make changes to the data, the columns name for this data i have separately, any ideas on how can i bring these two together without having to manually type in the column names, i have about 6 tables.  

 

I have tried to do it in power query by cleaning it up the columns names and then transposing it into columns names, however i am unable to append the data without column names to the cleaned up result because it expects the column names to be the same for the append.  I might be overthinking this and missing the obvious

 

[DateID] int NOT NULL,
[MedallionID] int NOT NULL,
[HackneyLicenseID] int NOT NULL,
[PickupTimeID] int NOT NULL,
[DropoffTimeID] int NOT NULL,
[PickupGeographyID] int NULL,
[DropoffGeographyID] int NULL,
[PickupLatitude] float NULL,
[PickupLongitude] float NULL,
[PickupLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DropoffLatitude] float NULL,
[DropoffLongitude] float NULL,
[DropoffLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PassengerCount] int NULL,
[TripDurationSeconds] int NULL,
[TripDistanceMiles] float NULL,
[PaymentType] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FareAmount] money NULL,
[SurchargeAmount] money NULL,
[TaxAmount] money NULL,
[TipAmount] money NULL,
[TollsAmount] money NULL,
[TotalAmount] money NULL
)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




2 REPLIES 2
101Mathew
Resolver II
Resolver II

In Power query can you not demote the columns titles to enable you to clean up the names, then pivot and re-name them if required?

@101Mathew still left with the issue of the columns names not match so if you want to merge / append the data you need matching columns





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.