Extract Values From Records And Lists In Power Query

preview_player
Показать описание
In this tutorial, we’ll take a look at how you can easily extract a single value from a record or list in Power Query.

Melissa

***** Video Details *****
00:00 Introduction
00:52 Custom column with Record.Field
01:45 Custom column with field access operator
02:25 What to do if field name doesn’t exist
03:05 How to extract record field value inside a list
03:45 How to identify zero-based position
05:02 Using the combination of item and field access operator

***** Learning Power BI? *****

#EnterpriseDNA #PowerBI #PowerQuery #PowerQueryTutorial
Рекомендации по теме
Комментарии
Автор

Enjoyed? I am ecstatic. This is the solution I to a power query problem that was seeking for the past 2 days. Extracting a particular record from an All Rows 'Table'. In this video I found the perfect solution. I am so very grateful for this simple and easy to understand tutorial. Thanks again.

jmathew
Автор

Been trying to fugure this out for a whole day, thank you so much

seunadewola
Автор

Thanks a lot....just a question....does the extraction have a drastic impact on refresh time??

royeden
Автор

Solid gold. So many valuable techniques and concepts packed into seven minutes, with crystal clear explanation. Thank you so much for doing these.

brianjulius
Автор

Great video Melissa and team. I’m working my way through the whole PQ playlist! Great to hear a kiwi accent on YouTube Sam.

Video was super clear except when using index column to extract from the list. We didn’t get to see what the lists contained so I couldn’t understand why the index column was required.

Jamieemmart
Автор

Thanks is there a best practice for extracting all columns from a record without hard coding names?

annemena
Автор

I was looking for this all over internet. Thanks!!

sanjayjob
Автор

Really Great Tutorial, Excellent Clear Explanation...Thank You Melissa

darrylmorgan
Автор

This is brilliant Thank you Melissa! I love your delivery style too. I'll definatley seek out more of your videos.
Concise and

paulgallagher
Автор

Short, concise & amazing tricks. Power Query expert at its best :)

mudassirsyedrashidali
Автор

You have explained this simply and precisely - thank you so much for just doing this so well.

tomn
Автор

Hi, this is amazing!! thanks for sharing. It works fine until there's duplicate field values on the list records and throws errors for the subsequent rows. For my use case Those are not duplicates from the table perspective but they are if I'm just looking at the list. Have you encountered this error with this process? Please let me know if you have any suggestions or workarounds.
Thanks!

ek
Автор

This is really useful and gets me part way down the path of what I need. I am bringing in a Microsoft List into PowerBI that has a multi-select choice column. That choice column comes across as a list of values for the multiple selections. How do I extract all the values so that I can run analysis on the Microsoft List that properly counts the unique choices in the choice column (so I have 100 rows, 90 of the rows have a single value in the choice field, 6 have 2 selections, 3 have 3 selections and 1 has 4 selections). If there are 4 possible selections, I am going to have 115 values across the 90 rows. There are 4 possible choice values, and I want to show a chart that shows the total times each choice was selected. Without any transformations of the data, PowerBI sees each combination as its own selection. What is the best way for me to get the data into the state that I need?

davidcarrico
Автор

Thank you, that was very helpful indeed! Helped me reduce 4-5 steps to achieve the same goal!

gerrythomas
Автор

Great video Melissa. This was an excellent guide to solving some SharePoint List import issues I encountered this week. Many thanks...

wken
Автор

I have a couple of tables in power query and want to pick a single value from table 1 and insert it as a new column into table 2. could you help me how to do it?

ahai
Автор

Thank you so much! This was exactly what I needed. 💕

AnaChavez-Loveannita
Автор

Thank you very much. I am here searching to extract "Hospital_Code_*" where * is wild card from 1000s of queries loaded from the PDF. Is it possible

granand
Автор

Hi Melissa, Thanks a lot!! I gotta question however. For the method "Custom column with field access operator" you show that an added "?" in the formula will return a "null" instead of an "Error" in case the field does not contain a value. However, I try to apply method 4 "Using the combination of item and field access operator" to retrieve field values of a records nested in lists, but the trick with the "?" doesn't work here. How can I avoid the error there? Thanks

t.shoemann
Автор

Hello, quick clarification - suppose I have a dataset with random text including ticket numbers which needs to be extracted however there is a chance that two or three sequential numbers could come as reference like for example in comment "ticket-4591 datatasktype" could match with search keywords of "ticket-4", "ticket-459" etc... Do you have any suggestions to avoid this?

zicoxavier