HELLO EDNA - looking for some help with a power query challenge. unable to move column, first 6 rows into new columns.
would like this:
to look like this:
tried for hours…no luck.
column 1 rows 1-6 should be header (6 additional columns)
column 1-8 row 7 should be header
column 1, row 8 and 9 should be header two separate columns (text and numbers) (this should give us two additional columns.
sample data attached.
thank you for any help in advance,
PowerQRows2.xlsx (47.0 KB)
Frankee
@Frankee
I believe I have solved your challenge.
The trick is to break it down into pieces.
Step 1
Step 2
Step 3
Step 4
Step 5
Hope this helps
PowerQRows_JC.xlsx (53.5 KB)
Hi @Frankee,
If you don’t mind a bit of coding, here’s a slightly different approach. Just copy the full code into a new blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZZrc5pAFIb/SoZ+rE1YYKHWT5rUiTMZc207HZsPRFHwAspFxV/ffTcaNwp7RsUF3j3n8D57gMHAeJj7cf7DaBidr89ie/p9bQyMu2To51ESQ/VSo/lz/wWnmSk/30zze43wOlks/bi86PuLADO25a5G2U2DLLxoRylkf4OsLmDqxzJSP6lRtHs3Ytge5jdBNhSjhzQQWzFvnIv/26RIETuLdjh8K3f6SS4SYnKvUxF1H5iZphhuVu+HOnfSALHx8DOPKiaGQaqorIOKOYrMEsO8JGW2GJYFKXPEsIhIGRfDKCFlrhguB9SFejqvqlbE/mTvuRNfa+bKZH5GpGfweTSmVLB5Er6rHpGVKb+9Bh6HUyWSfYhkMSUUPJ7OFBk/yGyuyODxbE7VBYvnLX1dmNP6V5im5VHIGPzebdV45iGgomqK4XZI1CYTDNdKLL4PxY8a7L6tqUjWOeOPs7+JZWABS7ygUoDKoqE10kLdl1eqh/zgoadcE5isfP11Y9LmbG2e+GyBRjCiSm9q3Hm57z7p/ZGLNKUawEZp+URRVS5uG6TKkAoGJMWUUgFJNCNTwtWEahRb3otalAr7g7NWYR+YzSNCG3D8nbpiKhDaTUVVm1d2YLalUjpMQ/rpl56znDyimtYBwDHVkA4ATt4oNA4IhrFqkXHaVA4ub6q2Z+WNyQG/2af+rHDbwbT5JVU9yLWuKJWurXpdvdnyRr7ZEik4yl99WkJn/nAACVRslf5wEEnXpAxEchIcB5RSD46DSEHdVzn2ew2yLiBJLgm8HEiWFDjX1IBjrq6HmKt70vwknjQuGPgbqjwgyAJKBQIj9fWvEpQLCPKN1Kh91riYNCmphCAQFmRCIJhG2oQy6ox8PfQwa74kmHsgssuoujx4v9U9Kl//Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
KeepTopRows = Table.FirstN(Source, List.PositionOf( Source[Column1], "Crane:" )+1 ),
TransposeTable = Table.Transpose(KeepTopRows),
PromoteHeadersA = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true]),
FinalTableA = Table.SelectRows(PromoteHeadersA, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
RemoveTopRows = Table.RemoveFirstN( Source, List.PositionOf( Source[Column1], "Crane:" )+1 ),
PromoteHeadersB = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
AddIndex = Table.FillDown( Table.ReplaceValue( Table.AddIndexColumn(PromoteHeadersB, "Index", 0, 1, Int64.Type), each [Index], each try if Number.From( [AID] ) >0 then null else [Index] otherwise [Index], Replacer.ReplaceValue,{"Index"}), {"Index"} ),
TableAct = Table.RenameColumns( Table.RemoveColumns( Table.SelectRows( AddIndex, each try if Number.From( [AID] ) >0 then false else true otherwise true ), List.RemoveLastN( List.Skip( Table.ColumnNames(AddIndex), 1), 1)), { List.FirstN( Table.ColumnNames(AddIndex), 1){0}, "Act" }),
TableB = Table.SelectRows( AddIndex, each try if Number.From( [AID] ) >0 then true else false otherwise false ),
MergeTables = Table.NestedJoin(TableAct, {"Index"}, TableB, {"Index"}, "TableB", JoinKind.LeftOuter),
FinalTableB = Table.RemoveColumns( Table.ExpandTableColumn(MergeTables, "TableB", List.RemoveLastN( Table.ColumnNames(TableB), 1), List.RemoveLastN( Table.ColumnNames(TableB), 1)), {"Index"}),
CombineTables = Table.AddColumn(FinalTableA, "Temp", each FinalTableB),
ExpandTable = Table.ExpandTableColumn(CombineTables, "Temp", Table.ColumnNames( FinalTableB ), Table.ColumnNames( FinalTableB )),
FilterRows = Table.SelectRows(ExpandTable, each ([Craft] <> ""))
in
FilterRows
I hope this is helpful
Hi @Frankee
If you know how to reference a cell, its easy.
Create a record(Custom column) as below, Just in one step you will get all the required columns.
First Few Rows to Columns.pbix (36.9 KB)
Hey JC - thanks for looking into this for me. I was unable to follow @ this step “table”
I saw the filter for 6 row…but could not figure out the “Table1” step. how do you create this step?
Hi @Frankee,
The code for that step reads:
Table1 = Table.SelectRows(#"Changed Type", each ([#"Plant:"] = "B+S"))
So in the UI a filter was placed on the colum [#“Plant:”], you can recreate this via the arrow in the column header, which will show a context menu (very similair to how you would place a filter in an excel table). The name of the step generated by the UI was renamed, to make it easier to find and refer to later on.
I hope this is helpful
Hey @Melissa - thanks for your replay and help. I’m going to try all three. right now I’m hung up on the first solution.
so How do I create the 2nd step. In that 2nd step , I’m to remove top 6 rows, but I don’t see that…I’m only left with one row due to the filter in table 1.
Great stuff…
Do you have the formula bar visible on your screen? If not, go to the View tab and toggle it on 
You’ll see the code for that step reads:
= Table.Skip(Source,6),
Source is a table reference, you’ll find that all the way at the top in the Applied Steps pane. You can refer to any step or even another query - again this code was manually adjusted.
If you select the operation/transformation/action on the ribbon (Remove Rows/Remove top rows), you can adjust the code in the formula bar afterwards. That way you don’t have to write or know all the M functions 
@Melissa , sorry but I don’t follow. I do see the tool bar.
I need to remove 6 rows. but I only see one row.
looks to me like I have to get back to before “Keep First Rows”…which was the 1st step.
Okay I know that’s confusing…
Just select Remove Rows / Remove top rows (and enter a 1 in the dialog box for example)
You’ll get an empty table BUT can now adjust the syntax in the formula bar into: Table.Skip(Source,6)
Calling the Source variable will bring back that table
Hope that helps
Excellent!
Looking at your steps you should replace Source with #"Changed Type"
To revert back to the table you used in building Table1.
All the best.
Melissa - appreciate the help as always. managed to figure out the “Merge Queries” step. Table I was on…3 only found it self. but I was able to update the table number in the formula bar…and it worked like magic. I partly wanted to go thru the manual , UI because my upload to EDNA was modified. it all worked out. so thank you al for your help.
Frankee.
