Power Query to move 24 values

mikemck

New member
Joined
Jan 14, 2019
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2016
I have a data set that is 240 rows in columns A & B. I have a third column that is 24 values and these need to be moved in 10 row increments to cover the 240 rows.
So, value 1 is actually value 10, 2 is 20, etc, etc to 24 which needs to be moved to row 240.

Is this possible with Power query ?

Thank you.
 
Attach a sample workbook
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.
 
Sample Data

Excellent, I will do just that. I have attached a sample with this post.

Thank you.
 

Attachments

  • Example Data.xlsx
    18.3 KB · Views: 16
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    ColD = List.Combine(List.Transform(List.FirstN(Source[D], each _ <> 0), each List.Repeat({null},9) & {_})),
    TheEnd = Table.FromColumns(Table.ToColumns(Table.RemoveColumns(Source,{"D"})) & {ColD}, Table.ColumnNames(Source))
in
    TheEnd

See attachment
 

Attachments

  • Example Data_PQ_BS.xlsx
    29.1 KB · Views: 27
Nice, Bill!

Can you talk us through the Co1D step, please? I'd like to understand exactly how it works.
 
That is just incredible. I have been working my way through M is for Data Monkey, and have been trying to get this done for way more hours than I care to admit.

I thank you very much.
 
(With apologies to Ken), there is another, more recent book really worth getting by Gil Raviv - if you find his Datachant blog, you can find out about it.

Hoping that Bill will come back and explain for the benefit of those still learning.
 
Much appreciated. I'll check out Gil Raviv's book as well. I'm making progress, but I have a very long way to go.

I need to ask for help again, as a matter of fact.

The sample data I posted was the result of an existing query that I had managed to put together. I did not realize that it would have made more sense to post the raw data, and what I was looking to transform it into.
Anyway, I now have two queries, and I cannot combine them. I have spent the last few hours trying to this seemingly simple task, but have had no luck.

Attached is my excel workbook with the raw data, and both queries.

If someone could explain how to combine them or post a completed query, I would be most thankful, again.
 

Attachments

  • PQ Battery Trend Report.xlsx
    54.4 KB · Views: 13
combined

:)
 

Attachments

  • PQ Battery Trend Report.xlsx
    41.8 KB · Views: 13
Gue - no explanation. Yes, well done - it's great - but it's useless as a teaching tool.

I had rather hoped that Bill would explain his, and I PMd him to ask him to do so. He has not responded to that PM or to my request above. :(

The point about this forum is not to show off what you are capable of doing, but to help others to understand HOW you did it so that they can use the techniques themselves.

So, Gue - are you able to talk us through the Co1D step, please? A smiley face tells us nothing useful.
 
Well, I am afraid that I have to say I find it very, very disappointing indeed that anybody, let alone TWO members, would be unwilling on a help forum to share their knowledge. This is really bad form. :(
 
Please disregard....Posted here by mistake.
 
Last edited:
Hi, Ali

Absent Bill's explanation...Here are some examples of the key individual steps:

Code:
Query Name: PickListItemsUntilZero
M-Code:
let
    Source = {1,20,10,0,0,0,0},
    SelectItemsUntilZero = List.Select(Source, each _ <> 0)
in
    SelectItemsUntilZer


Code:
Query Name: Append2ListsWithAmpersand
M-Code:
let
    SourceA = {null,null},
    SourceB = {1},
    AppendLists = SourceA & SourceB

in
    AppendLists

Code:
Query Name: InsertNullRowsAboveEachItem
M-Code:
let
    Source = {1,20,10},
    InsertNullBeforeItems  
        = List.Transform(
              List.FirstN(Source, each _ <> null), 
              each List.Repeat({null},2) & {_}
    )
in
    InsertNullBeforeItems

Code:
Query Name: CombineListOfLists
M-Code:
let
    Source = {1,20,10},
    InsertNullBeforeItems       
        = List.Combine(
            List.Transform(
                List.FirstN(Source, each _ <> null), 
                each List.Repeat({null},2) & {_}
            )
        )
in
    InsertNullBeforeItems

Does that help?
 

Attachments

  • Example Data_PQ_BS Steps.xlsx
    39.7 KB · Views: 10
Sorry, Ron - that's not quite what I meant. I can break down the steps myself. What I want to understand is the logic behind it. I need an EXPLANATION, not a break down. Does this make sense? It's just the CO1D section that I need talking through.
 
Oops - I thought I had said thanks, but it appears not. So, thanks, Ken, for the information, which I hope will help those who can't yet break down the steps themselves. :)
 
Hi, again

Bill writes some elegant code, but it's not entry level. When I use his approaches in solutions I create for others, I usually break them into several steps to the workbook owners can understand and maintain them.

I understand what you're looking for, but the answer is complicated. It would be easier if we could screen share and converse. But, since we can't do that I took a shot at expanding the step descriptions. Let me know if this helps.
Ok...

Using this annotated code:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
04    ColD = List.Combine(
03              List.Transform(
01                List.FirstN(Source[D], each _ <> 0), 
02                each List.Repeat({null},9) & {_})
            ),
07    TheEnd = Table.FromColumns(
06                Table.ToColumns(
05a                    Table.RemoveColumns(Source,{"D"})) & {ColD}, 
05b                    Table.ColumnNames(Source)
              ),
    #"Reordered Columns" = Table.ReorderColumns(TheEnd,{"B", "A", "C", "D"})
in
    #"Reordered Columns"

Summary:
01-Get the items that do not equal zero
02-Create a list of 9 nulls and the number for each non-zero item
Code:
    List1
    null
    null
    .
    .
    number

    List2
    null
    .
    .
    etc
03-Replace the original Col_D items with the null padded lists
04-Combine all of the Col_D lists into one list
06-Create a list of lists containing the 05a column values
and the column names from 05b
05a-Remove Col_D from the Source and replace it with the list items
05b-Get the column names from Source
07-Convert the lists back into a table


Section Descriptions:
01-Get the items that do not equal zero
Code:
List.FirstN(Source[D], each _ <> 0)
Returns each Col_D item until in encounters a zero

02-Create a list of 9 nulls and the number for each non-zero item
Code:
each List.Repeat({null},9) & {a_Step01_Item}
Builds a list for each record that inserts 9 nulls
before each of the Step01 items

03-Replace the original Col_D items with the null padded lists
Code:
List.Transform(Using_Step01_Items, Step02_Item)
Replaces each of the NonZero items (Step01) with
the null-adjusted items (Step02)

04-Combine all of the Col_D lists into one list
Code:
List.Combine(Step03_List_of_Lists)
Converts the many lists from Step03
into a single list containing all of the items

05a-Remove Col_D from the Source and replace it with the list items
Used as a source by Step06:
Code:
Table.RemoveColumns(Source,{"D"})) & {ColD}
Removes Col_D from the Source and attaches
the new column

05b-Get the column names from Source
Code:
Table.ColumnNames(Source)
Used as a Column Name List by Step06:
Lists the column names from Source

06-Create a list of lists containing the column values
Code:
Table.ToColumns(Step05a_Columns,Step05b_col_names)
Converts each column from the Step05a table
to a list of the columns values. In the example,
the list will contain 4 lists records
(1: Col_A values, 2: Col_B values, 3: Col_C values, 4: Col_D values)

07-Convert the lists back into a table
Code:
Table.FromColumns(Step05a_Lists,Step05b_col_names)
 
Thank you, Ron - this is very much appreciated. I will work through it a little later on. :)
 
Hi Ali
Sorry, I was out of net (and comp) for a few days. :-(((
I think that Ron's explanation is much better than i could have given :)
Thank you very much Ron.
For clarification, see my code below. (not as good as Ron's)
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],

// Get the list of nulls - number of nulls in the list is represents by the second argument of List.Repeat function
    ListOfNulls = List.Repeat({null}, 9),


// Get column D only - as a list
    D_ColumnOnly = Source[D],


// Get the firs N items of the list D_ColumnOnly - to the first item of the list that doesn't meet the condition  _ <> 0
    FirstNFromD = List.FirstN(Source[D], each _ <> 0),


// Transform each list item to list of nine nulls plus the value of the actual item
    TransformList = List.Transform(FirstNFromD, each ListOfNulls & {_}),


// Combine List of lists - the result is one list
    ColD = List.Combine(TransformList),


    TheEnd = Table.FromColumns(Table.ToColumns(Table.RemoveColumns(Source,{"D"})) & {ColD}, Table.ColumnNames(Source))
in
    TheEnd
 
Thank you very much to everyone in this thread. I am completely amazed at the capabilities of PQ and PP. I am more determined than ever to become proficient and hope to be able to contribute to here.

If you knew how much time and aggravation this solution will save myself and several others you would be surprised.
 
Back
Top