cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DerekReed
New Member

Add column in PowerQuery with data from specific row and column

Hello,

I'm working on a data model using data from MS planner. The extract of the data contains 4 rows at the top with some data about the extract, then row 5 contains the headers, then the rest has the data. It's easy enough to remove the top 4 rows then promote the column headers. But, my problem is I need one piece of data that is in the top 4 rows, in this instance it's the Plan ID. I've pasted in an example dataset below - the item I need is in red, and the blue ones are the column headers:

 

Plan nameSpring 2021   
Plan IDcf0uNgqczU6fOY-xhhv0vJYAD5tj 
Date of export20/01/2021   
     
Task IDTask NameBucket NameProgressPriority
tiBxb2zhK0SoMc9GHf7bLZYAM6XCAgree checklist of activities needed for each siteTidy up sharepointNot startedMedium
yLo7stxZyEeTYbeWkaTx3JYAGjYtGet list of sites no longer neededTidy up sharepointNot startedMedium
gT7iQmHL3UWbRwpbwq33R5YAPc63Set up meetings going forwardCross-department MI producers groupNot startedMedium
bSdeELP6oE2D67hiDnoWEZYAJ0c6Get an overview of expected timelines for productionAnnual report and accountsNot startedMedium

 

What I hope to do is add a column for all records that contains the plan ID, and then remove those top 4 rows. So the output would look something like this:

 

Plan IDTask IDTask NameBucket NameProgressPriority
cf0uNgqczU6fOY-xhhv0vJYAD5tjtiBxb2zhK0SoMc9GHf7bLZYAM6XCAgree checklist of activities needed for each siteTidy up sharepointNot startedMedium
cf0uNgqczU6fOY-xhhv0vJYAD5tjyLo7stxZyEeTYbeWkaTx3JYAGjYtGet list of sites no longer neededTidy up sharepointNot startedMedium
cf0uNgqczU6fOY-xhhv0vJYAD5tjgT7iQmHL3UWbRwpbwq33R5YAPc63Set up meetings going forwardCross-department MI producers groupNot startedMedium
cf0uNgqczU6fOY-xhhv0vJYAD5tjbSdeELP6oE2D67hiDnoWEZYAJ0c6Get an overview of expected timelines for productionAnnual report and accountsNot startedMedium

 

I plan to merge data from multiple plans together, so need something against each one to know what plan it relates to.

 

If anyone can help me with how I could do this I'd be really grateful.

 

Many thanks

 

1 ACCEPTED SOLUTION
2 REPLIES 2

Thanks @Gabriel_Walkman . That is exactly what I needed. Got it working perfectly now. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.