Power Query - Extract values from lists and records in adjacent column

Power Query
1

Hello all

I am attempting to use Power Query to clean data from SharePoint and would appreciate input to help extract record and list values without creating all sorts of duplicate rows or messy merges.

I would like to be able to create two new columns, side by side with the existing columns. For each row of the SharePoint table I would like to achieve the following:

Goal 1 - The first new column would show the extracted value for the Label row from the record. Image 1.

Power Query Image 1

Goal 2 - The second new column would navigate through the list, to the record and then extract the value for the “Title” row. Images 2a & 2b.

Power Query Image 2a

Power Query Image 2b
Power Query Image 2b489Ă—514 6.69 KB

Can someone please suggest m-code that might achieve this cleanly?

Thanks for your time.

2

Hi @Mo.jo.jo,

Use Record.Field to return a value from a specific field in a record.

Copy this into a new blank query.

let
    Source = #table( type table[Company=record, Created By=list], 
        {
            { Record.FromList({1, "Bob", "123-4567"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            },
            { Record.FromList({2, "Jim", "987-6543"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            },
            { Record.FromList({3, "Paul", "543-7890"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            }
        }),
    AddLabel = Table.AddColumn(Source, "GetLabel", each Record.Field([Company], "Label")),
    AddIndex = Table.AddIndexColumn(AddLabel, "Index", 0, 1, Int64.Type),
    AddTitle = Table.AddColumn(AddIndex, "GetTitle", each Record.Field([Created By]{[Index]}, "Title"))
in
    AddTitle

.
I hope this is helpful

4 Likes
3

@Melissa
Just how do you do that :sparkles::raised_hands:
All clear. Thanks so much for creating a relatable example from “thin air”. So helpful.
We are very lucky to have you as one of the experts here.
I heard Sam mention on a webinar that you plan to have a course out this year. Much anticipated.
Thank you.

2 Likes
4

Hi @Mo.jo.jo,

Great! Glad I could help :+1:
You heard correctly, working hard to create multiple PQ courses from beginner to … who knows where it will end LOL.

4 Likes
5

Very good news!!!
And you are right. It probably won’t end; we have a lot of questions and scenarios we haven’t even thought of yet :sweat_smile:
But at least you are partly-prepared for that :laughing:

1 Like
6

Hi @Mo.jo.jo,

I thought your question would make a nice topic for a video.
Hope you enjoy it!

4 Likes
7

Really looking forward to the coures @Melissa, really enjoying the Power Query series that is in the training hub. Started it yesterday and hopefully finish it over the next few days!

1 Like
8

That’s great to hear @DavieJoe. Thank you so much for your feedback.

As for courses on Power Query and M that will be multi year endavor, so depending on you current level you’ll have to be patient but I’m hopeing there will be some good stuff for everyone in the end.

All the best.

1 Like
9

Happy to be patient as the current content is fantastic and so will the future content be.

10

@Melissa
I did enjoy it. Thanks for putting a video together on that topic and also for the alternative approach. It was like a private YouTube tutorial :grinning: A good reference resource to have.

2 Likes