excel - VBA function to copy into new rows depending on the colum values

Question

I`m not a super experienced VBA developer and mostly relly on the Macro recorder, hence would appreciate any help by the community in helping me wrap my head around this problem. I havent used loops in the past but imagine this would be the best application for my problem.

I have the following Table;

Name	Year	Sec A	Sec B	Sec C
Joe	2020	15	20	30
Mary	2019	5	25	0
Peter	2020	7	0	0

Answer 1

@BigBen's comment is right.

In Excel, highlight your source table, choose Insert Table (or press ctrl-t) making sure you check that your table has a header row.

Then, in the table ribbon (when your cursor is in the table) rename your table to "Source"

Then, in the Data ribbon, in the "Get & Transform" section, click "From Table". This will create a query that pulls from this table, and present it for editing in the Power Query Editor.

In the Home ribbon of the Power Query editor, click Manage - Reference. This will create a new query that uses/starts with the current one. I recommend renaming it (in the right sidebar).

In the home ribbon of the Power Query editor, click Advanced Editor and paste the following:

let
    Source = Source,
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Sec A", "A"}, {"Sec B", "B"}, {"Sec C", "C"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name", "Year"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> 0)
in
    #"Filtered Rows"

You'll now have what you want.

Don't be scared of that code, by the way. I didn't really type all that! After creating the second query,

• I double-clicked the column headers to rename them.
• I highlighted the last three columns and clicked "Unpivot Columns" from the Transform ribbon.
• I clicked the filter for the "Value" column to only get rows where Value wasn't 0.

and that was it!