Dynamically Find a Column Header in Power Query | Dynamically Select Column Header In Power Query

[Music] hey there welcome to biz intelligence let's quickly take a look at how to dynamically select column headers in power query if you use power query in excel or power bi and when you import data source you need to make sure that you import actual required data and you exclude garbage rows so we have one data set called sales data and you can see there are few rows at the top and there are few blank rows and then we have actual data set here so this is the data set that we are gonna import in power query and we will make sure that we exclude these garbage rows now this time this data set starts from row number 10 and tomorrow it can start from row number five or row number 15 as well so this is not fixed so we need to make sure that we refer to correct data source from the correct row so i have already loaded this data set in power query and there are few steps already applied on this data set these are the default steps so i will go to this promote headers tab where we have headers promoted and data type is changed for these columns so i will go to promote headers i will click on delete until end to delete rest of the steps from promote headers next i will go to add column and i will add index column i will click on index column from 0 and let's rename this column as row underscore number now what does index column do it adds a column with numbers starting from zero incremented by one so you can see the row number for each of these lines next step that i'm gonna do is i will go to this column number one and here you see our data is starting from here and in column one we have brand so i will go to this down arrow and i will select only brand and this will filter the data and here you can see we have one row where column one value is brand now i will go and delete this open bracket i will delete this close bracket as well and here i will say in square bracket row underscore number which means that give me the value of row number field where column one sale value is equal to brand so i will get number nine from this and i will press enter this means that we want to skip nine rows before header row so let's take a look at this data set yes so we have nine garbage rows before our data and these rows we need to skip if we add few more rows or let me delete few rows now we need to skip seven rows as we have removed few deleted few rows let's save this file and go to power query editor and go to home tab and click on refresh preview refresh all and you can see number is changed so this is working correctly now what we need to do is this returns a list of numbers instead of list i want a single value so i will go to this row number and post that i will write curly bracket 0 this means from this list giving me the value of 0th position so list starting from zero and we will get the first value of that list which is number seven after this i will go to this function sign where i can add new step and here i will write table dot skip so what does table dot skip does is it returns a table with the first count of row scheme that means it removes first few rows from the table and returns you the result it takes two arguments one as table and one as count of row that you want to skip from the table so first we need to provide the table which is here in the added index field you can see this filter rows written returns us the row number and this added index returns as the table so i will use added index added index so this is the name of that step which we are using here what does it mean that we want to use the result which is written by added index table let's give comma here and this is going to be the row number and then press enter and you can see we have actual data available now let's go and remove this index column and let's go to home tab and click on this use first row as header row and our data is ready now let's go and add few more rows here with few values let's press ctrl s and add few more rows ctrl s to save let's go to power query editor let's click on refresh view all and still we have correct data referred in the power query so this is how you can dynamically select column headers in power query if you are new on our channel then subscribe us and visit our channel to watch more such videos do not forget to like and share this video with your friends thank you for watching you

Channel: Bizz Intelligence

Views: 10,451

Keywords: bizz intelligence, dynamically find header rows and promote them in power query, find table headers in power query, pqf: table.selectrows; table.skip, table.selectrows power query, pqp: find headers dynamically in power query, power query dynamic column headers, power query dynamic column name, power query dynamic column reference

Id: Ys_Qfyvyh8o

Length: 5min 28sec (328 seconds)

Published: Sat Sep 04 2021