Unstack Column

Hi rayhannon ,

Maybe you can try the following steps and the related codes in advanced editor in power query editor and I'll help you to understand:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVLLbsIwEPyVVc4E/IgT4GaCG1zyoIlpC4hD2iJRCbVS2h74+yZOnCgCpEo9emd21jO7u51FKXKsgWXtBztLHX+Kr7f8DAyivHg9AkEEGRDTqUOmxDPvZV4cPr6Xh9PpbEp+EiZpxJW/kHGATfX+8wUIowhkAAsOcxu1GpmIJERcKsCuTYgpXxJV8Z6frghgTOAuhJivuFokIaRiXkJoiKiLNIFovCxD8iznAjajTBMwYw3BqwgPaxnHbTdGqAap7l6J0lQsYgVJyuNA1BMMx0GdQMSDksabKWTIWpIWkmnAt7Kb02mMK3gjwjB5gtAm2EE1wTWEibGRKttjk7qfmH6m/zAbZe0P+vo6XWjSVqoE3c7kbQO4UzCBU8/5+7mwKR3//1xIq+H7sjfjkqHvhF5pLd1WLoNUiLgKqp8fpRXo83Qmt+UVPcokFKofwO2UnKHT7kHrrLNmlc0p4ltLKOWdqnP/Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let i = [Index]
in
Table.RowCount(Table.SelectRows(#"Added Index",each [Index]<i and [Column1]=null and [Column2]=null))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Data", each _, type table [Column1=text, Column2=number, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "ShapedData", each let  #"Sorted Rows" = Table.Sort([Data],{{"Index", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Custom"})
  in
  Table.SelectRows(#"Removed Columns",each [Column1] <> null or [Column2] <> null)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each let job = [ShapedData]{0}[Column1],
dt = [ShapedData]{1}[Column1] & " "&[ShapedData]{2}[Column1]
in
Table.Skip(Table.AddColumn(Table.AddColumn([ShapedData],"Job Number",each job),"Date Time",each dt),3)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Data", "ShapedData", "Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Column1", "Column2", "Index.1", "Job Number", "Date Time"}, {"Custom.1.Column1", "Custom.1.Column2", "Custom.1.Index.1", "Custom.1.Job Number", "Custom.1.Date Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1.Date Time", type datetime}, {"Custom.1.Job Number", Int64.Type}, {"Custom.1.Index.1", Int64.Type}, {"Custom.1.Column2", type number}, {"Custom.1.Column1", type text}})
in
    #"Changed Type1"

1. Replace values from " " to "null":
2. Add an index column to mark each row:
3. Add a custom column to distinguish different data blocks:
4. Group by the previous custom column:
5. Add custom columns to extract columns and skip some columns:
6. Reomove unnecrssary columns and expand table:
7. The final table is like this:
8. To count colors by date / job number, just create a table visual and apply corresponding filters in the table visual:

All steps can be seen in the Applied steps in the right of power query editor and you can use the gear icon to test and check:

Finally, the sample file attached hopes to help you, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hi rayhannon ,

It's my pleasure🙂.

1. I didn't enter these total columns previously becasuse I thought they are used as a reference. If you want to import from excel, you can remove these columns directly and still maintain a blank row between two data blocks.

2. When creating custom columns, you need to use some formulas that power bi will prompt you if you enter the first few letters of the formula in Custom column page.

3. It is not essential to be able to write Power Queries in M except create some custom columns. As I previously posted, you can click the gear icon to see the detail actions in Applied steps and you can find the corresponding actions in the menu bar on the top of power query editor.

For example, using group by function just need to find the Group By icon in the menu. I put all codes in this post is just convenient to introduce each summary step.

If you are interested in M query function, you can refer these two microsoft documets as a simple understanding:

• Add a custom column in Power BI Desktop 
• Power Query M function reference 

4. Add Custom Column1 is to shapedata to remove the 'custom column':

Add Custom Column2 is to skip these rows which I thought is used as something about Job number and set them as new columns to skip these previous rows (not columns, sorry for previously post)

For this issue, I extract top3 rows and set them as two 2 new columns( It depends on your own decision) and remove these selected 3 rows

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.