Thanks for the video, sure there are many other ways to get this done using the UI, but your video showcases how to really use M, how to refer back to previous steps, how to break your steps into logical chucks. Best channel to really learn M language. Thank you sir
Great Video. What i usually use ( from GUI itself) is something like this: 1. Transpose (before promoting headers) 2. Remove Rows > Remove Blank Rows 3. Transpose back again.
Awesome video as always, thank you! I’m actually exploring writing the M code myself, instead of using the UI. It is actually working out quite wel, thanks to your videos. Awesome that we can combine multiple steps in 1 step when we write the M code ourselves.
Good I did the same but using a configuration file that lists expected column names and use that to clean my input file. Didactic suggestion that would make us at the beginning of your videos also state the lessons to be learned, and then just recap at the end. Excellent
Hi Chandeep. Excellent lesson! Another approach is from the reverse.. using Table.SelectColumns to specifically select only the desired columns, thereby filtering out everything else, as in: = Table.SelectColumns(Table.PromoteHeaders(Table.Combine(Source[Data])),{"Name","Age","Company"}). Of course, if you add more desired columns, you would have to modify the query for the additions. I guess it depends on whether you are more prone to adding new columns of desired data that need to be included or adding new columns of undesired data that need to be excluded. Either way, good to know both approaches. Thanks for sharing this! Always good learning at Goodly :)) Thumbs up!!
This was really helpful, however for some reason it seems to merge all data into a single table when there are multiple sheets in the excelworkbook. Is there a way to refer to a certain Item?
Sorry but the solution is not correct. You are removing not the empty columns but the columns which don't have any header name. These two are different issue. Sometime our column may have data, might be 10th or 20th row, in that case your formula will ignore that column, which is not correct. In my scenario by removing the whole column, you are removing some data which was not type properly. There should be some other option, which remove the column only if all the row in that column is nulll.
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Thanks for the video, sure there are many other ways to get this done using the UI, but your video showcases how to really use M, how to refer back to previous steps, how to break your steps into logical chucks. Best channel to really learn M language. Thank you sir
Great Video.
What i usually use ( from GUI itself) is something like this:
1. Transpose (before promoting headers)
2. Remove Rows > Remove Blank Rows
3. Transpose back again.
With large amounts of data, your process can become slow.
Just what the doctor ordered. Thanks!
Awesome video as always, thank you! I’m actually exploring writing the M code myself, instead of using the UI. It is actually working out quite wel, thanks to your videos. Awesome that we can combine multiple steps in 1 step when we write the M code ourselves.
Good I did the same but using a configuration file that lists expected column names and use that to clean my input file. Didactic suggestion that would make us at the beginning of your videos also state the lessons to be learned, and then just recap at the end. Excellent
Man, your videos are amazing and very didactic! Keep the good work!
Useful video. It happens quite often actually that I need to remove columns manually.
Hi Chandeep. Excellent lesson! Another approach is from the reverse.. using Table.SelectColumns to specifically select only the desired columns, thereby filtering out everything else, as in: = Table.SelectColumns(Table.PromoteHeaders(Table.Combine(Source[Data])),{"Name","Age","Company"}). Of course, if you add more desired columns, you would have to modify the query for the additions. I guess it depends on whether you are more prone to adding new columns of desired data that need to be included or adding new columns of undesired data that need to be excluded. Either way, good to know both approaches. Thanks for sharing this! Always good learning at Goodly :)) Thumbs up!!
Thanks a lot Chandeep! Very helpful!
Happy to help!
Thank you!, Learned a new thing today in Power Query.
Glad it was helpful!
Great tutorial! Thank you!
thx alot for your effort Really i had learned more Information from your Videos
Can I import a selected columns list that I have in Excel?
Nice!
🙋♂For data modeling, how do I keep only ColNames that have 80% or more of non-nulls?
Excellent tip. How to do when we do not yet have the header set?
Good solution buddy 👍
Hi Goodly - Why do you not transpose & remove blank rows & transpose again? Seems easier for blank column removal and also dynamic
That will only work for blank columns but if you have some text then is not possible I believe.
How we can remove empty columns from multiple tables, but without merging tables?
Great video! Suggestions about the following function, please:
(Tab as table) as table =>
let
LstOriginalColumnNames = Table.ColumnNames(Tab),
LstColumnListOfLists = Table.ToColumns(Tab),
TbConvertedToTable = Table.FromValue(LstColumnListOfLists, [DefaultColumnName = "ListOfLists"]),
TbIndexColumn = Table.AddIndexColumn(TbConvertedToTable, "Index", 0, 1, Int64.Type),
TbNonNullColsIndex = Table.SelectRows(TbIndexColumn, each List.NonNullCount([ListOfLists]) 0),
LstNonNullCols = Table.AddColumn(TbNonNullColsIndex, "NonNullCols", each LstOriginalColumnNames{[Index]})[NonNullCols],
TbOutput = Table.SelectColumns(Tab, LstNonNullCols)
in
TbOutput
This is fire Goodly - great!
Just Great, as usual!! Thank You.
Will you please make a video on finding Subtotal in Power Query like Subtotal in Excel pivot but not Group by?
Another very cool lesson
Thank you! :) But what if there is column(s) with other names then column and they still has only null in each row?
Thank you very much
Sir Great Help. Please tell me how i can show the Formulas or functions while Typing some part as in your video. I am using office 19.
This was really helpful, however for some reason it seems to merge all data into a single table when there are multiple sheets in the excelworkbook. Is there a way to refer to a certain Item?
You'll have the option to navigate to a single workbook in the Navigation step (typically after the source step)
The formulas you are typing in, is that the DAX language or something else?
M Language
Apne kaha this is simple data.....please make video on how to handle complex data in dax
Hi sir ,
Thanks you so much for 📷
Could you please make video how to fetch table from NSE INDIA website ?
Amazing
Why not just Transpose, Unfilter Blank, and Transpose again. Done.
I like that you don't use the user interface. It's the only way to learn M.
How to dynamically remove blanks rows?
I'd be keen to know how to delete columns where all the rows are empty but there is a normal header
Noted:) Need to put together a video for that.
Null records वाले कॉलम नहीं हट रहे । मदद कीजिए प्लीज।
Sorry but the solution is not correct. You are removing not the empty columns but the columns which don't have any header name. These two are different issue. Sometime our column may have data, might be 10th or 20th row, in that case your formula will ignore that column, which is not correct. In my scenario by removing the whole column, you are removing some data which was not type properly. There should be some other option, which remove the column only if all the row in that column is nulll.