Reply
Super User
Posts: 1,499
Registered: ‎07-27-2016

column names and data separate - append data to columns?

[ Edited ]

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
)

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Highlighted
Regular Visitor
Posts: 22
Registered: ‎01-16-2019

Re: column names and data separate - append data to columns?

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?

Super User
Posts: 1,499
Registered: ‎07-27-2016

Re: column names and data separate - append data to columns?

@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

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!