Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

my data dont have identifier or gap, it just separated by line/borders, how to unstack this kind of problem ?

A real world problem solved with Clarity and with the right pace. You just gone to my Number one InformativeList. Thanks

@GoodlyChandeep

Жыл бұрын

Thanks a lot Miguel.

How would you approach this if there was no separator/null field to target initially? My column is stacked without gaps that I can tap into, so would love to know how to achieve the unstack with this data set. Data is a mix of "category name" + "range name" + product barcodes. But there are uneven numbers of products in each range. I need to unstack the data into three columns Category + Range + Barcode. Any help greatly appreciated.

It's Great. I have not found any other tutorial on this topic so crisp clear.

@GoodlyChandeep

2 жыл бұрын

Glad you like it! 💚

Really impressive stuff here. Thanks for both unstacking videos.

Dude you just saved my life. It was not so easy for me, but i did it with ur help! Tks a lot

Dude, you are a lifesaver. This is going to cut back on a lot of man-hours for my team.

@GoodlyChandeep

3 жыл бұрын

Thanks Jonathan! Glad you found it helpful!

Just what I needed, thanks!

this was extremely helpful

@GoodlyChandeep

3 жыл бұрын

Glad it helped!

Wow this is so satisfying to watch and to learn more in depth how to unpack coloumns Power Query. It will definitely can help me at my work

This video is really very helpful...

Wow. Used modulo loads for fixed rows but always wondered how to do it for different number of rows. This tutorial has now answered it. I would never have been able to do this Goodly, thank you for sharing.

@GoodlyChandeep

4 жыл бұрын

Glad it was helpful! :)

Thanks you are a master.

Great Solution. Well done.

Man this is just great work cheers man

Very good solution, i liked! Thank you!

@GoodlyChandeep

Жыл бұрын

You're welcome!

Dude I had to come back to thank you again! This video is going to put me on the map at work :) Thanks again dude!!!

@GoodlyChandeep

3 жыл бұрын

Thanks Jonathan. I am glad I could help. Cheers!

Great 👍 Thank you

Thank you very much. Explained so effectively that I was able to modify your guidance for my particular situation.

@GoodlyChandeep

3 жыл бұрын

Thanks Jerry, glad you found it helpful!

@jerryowens8951

3 жыл бұрын

BTW When I go to group into tables My computer thinks for about two hours (literally) and then I get an error. Any thoughts? I have over 1,000 lines i'm attempting to organize.

@GoodlyChandeep

3 жыл бұрын

Can't comment unless I see it. Can you send me a sample of your data at goodly.wordpress@gmail.com

This Is Best Tutorial i have ever seen.

@GoodlyChandeep

4 жыл бұрын

Thank you.. Glad you found it helpful

Nice video. Thanks for sharing!

@GoodlyChandeep

4 жыл бұрын

Thanks for watching! :)

Thanks🙏 for making this vedio

You should be an Excel/PBI MVP

@GoodlyChandeep

4 жыл бұрын

Haha Thanks Anas! :)

@johnhackwood1568

Жыл бұрын

@@GoodlyChandeep I agree, this was masterful

This worked well until I got to the Grouping part. My first attempt was with a large database that attempted to group for hours. The second database had less than 600 records of no more than 11 elements in each. Still my computer spun for a long time. Found another solution. When you create the column with values of "null and 1"; with 1 as the first value of each record, I left PowerQuery and brought in the two columns into Excel, added another column and used the following IF formula to get my counts, using your example with the custom column (B) with Null/1 found around 2:10 in the video. First Cell B1 should have a 1 since it is the first item in the record. In a new column, Cell C1: "=if(b1=1, 1); in cell C2 I added: =IF(b2=1, 1, sum(b1+1)), then copied/filled down the C column. This gave me a sequential count for each record.

Amazing. You are the best..

@GoodlyChandeep

3 жыл бұрын

Thank you. I am glad you found it helpful!

Great video

Amazing 👌...This is crazy!!

@GoodlyChandeep

4 жыл бұрын

Thank you 🙌

I've created a quick timeline of the video: 00:00 Identify row patter, 4 rows for each block, blank 01:00 Load data to PowerQuery 01:09 remove automatic change data type step 01;11 identify where blocks start / end -in this example null rows 01:35 add a custom column to identify null values 02:13 Add an index column, starting from 1 02:39 create a row identifier column using List.FirstN() 04:20 test first generation result, look in generated LIST results 04:55 enhance the formula using List.Sum() to create row count 05:20 Problem is row count for row 1 is “null”, modify the formula to add “ + 1” 05:40 row 1 is still NULL, add IF() to the formula 06:13 Row 1 now has value 1, but rest are still null, Fill down, will only affect null values 06:25 summary, you have now generated a custom column identifying all data on same row in final desired resultg 06:38 remove null values using “Empty” filter to catch null and blank values 07:00 build new custom column with end result column identifiers 07:27 Use Add column tab > Group By function to create new column 07:53 Result is a new table with column name and new column with table results for each row 08:15 create a new column counter that restarts at 1 for each new rows using Table.AddIndexColumn() 09:26 Expand the new tables in the new column, filter to keep only original “column1” and new generated index column 09:44 Remove the “All” column it is redundant 10:22 Generate a Pivot Table on the Column identify custom column. . Now have the data in desired format, just need to do some “cleanup” 11:02 Remove the row identifier column 11:08 Need to rename columns, but problem is there is a dynamic number of columns using a “helper” table with column ID and Column name 11:54 Join the help table to pivot table. Note if you just rename the columns directly using Table.RenameColumns() you have a list of lists inside the parenthesis. First list is 2 parts, current col name and new col name, second list is list of all of the columns. Transpose the column name helper table 13:33 Transpose the helper table into a single column of lists using Table.ToColumns() to create list of lists 13:54 use the helper table query as input to the Table.RenameColumns() function in the main query. This makes column naming dynamic. Make changes in the helper table in spreadsheet, ie to add new column names 14:12 Last input to the customized Table.RenameColumns() is MissingField.Ignore . demonstrate adding new column value, adding new column name to helper table 15:50 Summary: make sure input data remains in same order. .

How can we sum of a table columns e.i. monthwise value on columns get their sum in new column

Great 👍💯

Hi Goodly, Fantastic technique. However, i have tried to replicate and the procedure crashes Power Query every time. I Cut the data down to 1200 rows but still painfully slow. Is this normal with big data doing this method.

Great video!

@GoodlyChandeep

4 жыл бұрын

Glad you enjoyed it

Amazing video. You are so natural with your breakdown and logic. If I wanted to take the grouping further - I have about 10-12 columns of information with about 10000 rows, how could I refine this further? I'm finding that the data when I pivot is scattered with many empty columns and data that becomes scattered with many null rows. Trying to 'patch up' the data afterwards is impossible! Is there a way to refine perhaps the Custom columns? Or maybe a way to extend 'Group By' to retain the original 10-12 columns? Thanks in advance! TM

Nice. Thank you for your video. Very well explained. New to PQ and I followed your tutorial. I wonder if Table.buffer could be used to speed up things up for larger data sets?

@GoodlyChandeep

3 жыл бұрын

Yes Indeed!

@YanalKamal

3 жыл бұрын

@@GoodlyChandeep Thank you. On to the next cleansing problem :)

Excelent. As with structured vertical list, attempted to unstack the rows without index except for creating the headers and take care of columns during transpose steps. The code is given below a bit long and the performance is to be checked for large data set. // FieldNames : a table in excel file let Source = Excel.CurrentWorkbook(){[Name="FieldNames"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field Names", type text}}), #"Field Names" = #"Changed Type"[Field Names] in #"Field Names" // FldCount let Source = Excel.CurrentWorkbook(){[Name="FieldNames"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field Names", type text}}), #"Field Names" = List.Count(#"Changed Type"[Field Names]) in #"Field Names" // TableHeader: for renaming the headers in the final step. let Source = Excel.CurrentWorkbook(){[Name="FieldNames"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field Names", type text}}), #"Field Names" = #"Changed Type", #"Added Custom" = Table.AddColumn(#"Field Names", "Custom", each "Merged"), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"),{"Custom", "Index"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"), #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Merged", "Field Names"}), #"Transposed Table" = Table.ToColumns(Table.Transpose(#"Reordered Columns")) in #"Transposed Table" // QueryHeader : Required to take care of auto "Merged" text let Source = Excel.CurrentWorkbook(){[Name="FieldNames"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field Names", type text}}), #"Field Names" = #"Changed Type", #"Added Custom" = Table.AddColumn(#"Field Names", "Custom", each "Merged"), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"),{"Custom", "Index"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged")[Merged] in #"Merged Columns" // UnEvnColRecd : the fun part let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"||",Replacer.ReplaceValue,{"Column1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}}), //PoistionofRecBreak = Table.AddColumn(#"Changed Type1", "Custom", each List.PositionOf(#"Changed Type1"[Column1],"||",2)), RowCount = Table.RowCount(#"Changed Type1"), ColNumber = {1..RowCount}, #"Converted to Table" = Table.FromList(ColNumber, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each "Column"), TransFieldList = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Column1", type text}}, "en-US"),{"Custom", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FieldName")[FieldName], #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Merged Columns" = Table.CombineColumns(#"Transposed Table",TransFieldList,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(",||,", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), QueryHeader), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",TableHeader) in #"Renamed Columns"

can we apply same steps in power bi power query to achieve dis ? does excel power query and power bi power query are same ?

@GoodlyChandeep

3 жыл бұрын

Yes PQ is same in Excel and Power BI.

nice discussion! What about if the unstacked, uneven records are also unsorted?

Hi chandeep nice see to you again How to achieve row level total in power bi bcz row level analysis available in tableau in power bi how we can achieve please provide solution 🙂

Great video. I have a twist to see if you can make it happen. How do you ID the record break if there is no null value, but instead is a repeated value such as a second phone number, or a third email address, or something with a consistent value but not a null.

Great videos and Thanks for sharing the knowledge. I have a question in Unstack / Uneven data with a twist: I have data like the following: Name John State TX Zip 92387 null Name Quin Street Sioux St City Mali State MN null Name Terry Street Main St City Nashua State NH Zip 73654 null Name Pam Zip 89765 State RI How to transform into: Name Street City State Zip John TX 92387 Quin Sioux St Mali MN Terry Main St Nashua NH 73654 Pam RI 89765 Note: Each record is separated by a null row. Fields in the record can come in any order. Fields can be missing. Thanks in advance.

I expect more complex, straight forward and M language Expressions. Especially I am stuck with stupid errors like Expression Error like Expression.Error: We cannot convert the value "Origin" to type Record. Details: Value=Origin Type=[Type]

@GoodlyChandeep

3 жыл бұрын

These are errors of structure / grammar! I'd suggest you to read the M documentation - docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

Great video!!✌ Single cell formula, for part 1: =LET(a,Table1[Column1],c,XMATCH("|",IF(a="","|",a))-1,f,FILTER(a,a""),r,ROWS(f),w,r/c,XLOOKUP(SEQUENCE(w,c),SEQUENCE(r),f)) Single cell formula, including the headers: =LET(a,Table1[Column1],h,{"Name","City","Age","Phone"}, c,XMATCH("|",IF(a="","|",a))-1,f,FILTER(a,a""),r,ROWS(f),w,r/c, x,XLOOKUP(SEQUENCE(w,c),SEQUENCE(r),f),s,SEQUENCE(w+1)-1, IF(s=0,h,INDEX(x,s,SEQUENCE(,c)))) Works for any data, table or simple array, that follows the pattern (blanks between records), and same number of fields, no refresh needed. Formula calculates alone nr. of fields and nr. of records, has only 2 variables, the array/table itself and the headers.

@GoodlyChandeep

2 жыл бұрын

This is nifty.. thanks for sharing!

@KgasS

Жыл бұрын

For Office365 beta users for the formula method the given one works =VSTACK({"Name","City","Age","Phone#"},INDEX(FILTER(Table1[Column1],Table1[Column1]""),SEQUENCE(COUNTA(Table1[Column1])/5,4))). This can be further simplied using let for the repeating elements. Thumps up for the second part. The mentioned formula is for the given data set :-> for the dynamic one =IFERROR(VSTACK({"Name","City","Age","Phone#"},INDEX(FILTER(Table1[Column1],Table1[Column1]""),SEQUENCE(COUNTA(Table1[Column1]),4))),"") This will suite for small data set.

@Excelambda

Жыл бұрын

@@KgasS Cool!! Nowadays with the new functions, this is how part 1 and part 2 can be solved (main array calculations, adding the headers require a simple vstack) -part 1: =LET(a,Table1[Column1],WRAPROWS(FILTER(a,a""),4)) -part 2, which is the challenging one, that works for any column table, capable of calculating nr. of records for each row , single variable, the table itself =LET(c,Table2[Column1], s,SEQUENCE(ROWS(c)), e,(c="")*s,x,FILTER(e,e),y,VSTACK(0,DROP(x,-1)),z,x-y-1, r,REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(v,i,LET(a,INDEX(y,i,1)+1,b,INDEX(z,i,1),VSTACK(v,SEQUENCE(,b,a))))), IFNA(INDEX(c,DROP(r,1)),"") )

@KgasS

Жыл бұрын

Part-1 array formula requires a small correction which may elimnate the IFERROR function, =VSTACK({"Name","City","Age","Phone#"},INDEX(FILTER(Table1[Column1],Table1[Column1]""),SEQUENCE(COUNTA(Table1[Column1])/4,4))). The advantage of CountA is it skips empty cells. Your solution on part-2 works for the given Data set if you introduce another data for example 3 below Accountant to mention the number of chlidren then it breaks. Part-1 solution provided still shorten the formula and thumbs up 👍. I read somewhere that if the length of formula is more than a thump then it is hard to understand and debug latter. Thanks

@Excelambda

Жыл бұрын

@@KgasS The formula part 2 works based on a pattern. The pattern "rule" of the data is that the records are separated by empty cells. The formula does not break if the pattern is consistent. Adding a cell excluding an empty cell makes the pattern not to be consistent, not the fault of the formula. You can add whatever cells keeping the pattern if you also insert an empty cell to flag the records. We cannot say that an adding formula is broken if we try to add text strings. As long as we have a pattern, any pattern, a formula can be designed based on it. A formula that does not work when the pattern is still consistent can be called a broken formula. My 2 cents. ✌

this is the error im getting at list.firstN calc Expression.Error: 3 arguments were passed to a function which expects 2. Details: Pattern= Arguments=List

@GoodlyChandeep

3 жыл бұрын

Can't say much unless I see your query. I am suspecting your error is in List.FirstN. Make sure that your've only passed 2 arguments.

Hi. I have an excel data file where there are changing headers between rows. Headers are "shoes sizes". Values are stock of each size. How can unstaack this? It looks like this: Store Style Brand Color Finish Suela Concept Sizes CHC MED GRA BODEGA 8551 PANTUNFLAS FINAS ROSA TEXTIL S/SUELA PANTUFLA DAMA 1 1 IXTAPALUCA 8551 PANTUNFLAS FINAS ROSA TEXTIL S/SUELA PANTUFLA DAMA 2 1 1 CHC MED GRA EXG IXTAPALUCA 3804 PANTUNFLAS FINAS LILA TEXTIL SINTETICA PANTUFLA DAMA 1 1 2 10 10.5 11 11.5 12 12.5 13 13.5 14 CHIMALHUACAN 1054 RILO BLANCO PIEL/NAPA SINTETICA BOTA AGUJETA NIÑO 10-12 1 IXTAPALUCA 1054 RILO BLANCO PIEL/NAPA SINTETICA BOTA AGUJETA NIÑO 10-12 1 2 MAYORAZGO 1054 RILO BLANCO PIEL/NAPA SINTETICA BOTA AGUJETA NIÑO 10-12 2 1 11 12 13 JARDIN 1210-B-AZ CACHORROS MARINO VAQUETA SINTETICA HUARACHE NIÑO 12-14 1 IXTAPALUCA 950509-C COQUETA NEGRO CHAROL SINTETICA BOTA CONTACTO NIÑA 10-12 2 1 JARDIN 950509-C COQUETA NEGRO CHAROL SINTETICA BOTA CONTACTO NIÑA 10-12 1 1 1 MOLINA 950509-C COQUETA NEGRO CHAROL SINTETICA BOTA CONTACTO NIÑA 10-12 2