Struggling to think of a way to generate 'blank' rows in a worksheet.

[Rhothgar]

Hi

I think I've gone as far as I can with my limited knowledge of power query.  I've been typing questions in to see if someone else has had this exact issue (which I am confident they will have) but I simply cannot find anything maybe I am typing the wrong question or using the wrong descriptive terms?

So basically I am working towards generating a spreadsheet that will automatically enter data into a Sage Accounts audit trail transactions template.

It needs to have some sort of if type is postage label and date.EndOfMonth does not exist then create one please, thank you very much equation...

At present, I think I am wanting to include end of month figures even if they are zero.

My specific issue is therefore in the following sample data although the sample data should run from 1st June 2024 to 31st January 2025 (I haven't included any June samples or some other months because I couldn't get the date format correct in the underlying sample spreadsheet) I have gaps where end of months are not appearing because the "Type" does not have any data in that instance.

It took me all of two hours to produce this sample data.  I'd also like to know how address and order data could be more quickly anonymised for future postings.  It took me longer to produce the sample data than it did to produce most of the queries!

So I am looking to create a dummy line with end of month date and a zero value to keep each query 9 rows deep as I intend to copy and paste the data from "Merge 2" into another spreadsheet manually.  The other spreadsheet allows me to reconcile the payments and receipts to a month end balance.

LINK TO SAMPLE FILE on OneDrive - Power Query Transactions to Sage 

Here is a screenshot of what I am trying to achieve (this is from my working example but some of the queries have gaps):-

Queries with no gaps.  This is essentially what I am trying to achieve on all sheets:-

The first one has no gaps because there are no null values.

The next screenshot is what I want to see on the tabs that don't show this format - one which has no gaps despite having null values:-

This one is missing end of months from August 2024 to January 2025 inclusive because no postage labels were purchased in those months but I would still like to populate those rows irrespective of data not being available.  The actual figures for those months is zero and should be shown as such:-

This is missing August, December and January rows because no data exists and so I need additional lines to bring total depth to 9 rows, the missing end of months together with the value I need to show is, once again, zero:-

The issue is probably caused by the way I have filtered the data.   You will see an Added Custom column and Filtered Rows step in most, if not all, of the queries as I am filtering using the word in the Type column but there are no postage labels or other fees in some of the months.

I simply cannot think of a workable solution to this problem.

I need to insert rows that don't even exist which is why I am referring to them as 'dummy' rows.

[PwerQueryKees · Accepted solution]

OK. I gave it a try.

I downloaded your Excel and built a query to geve you the result you seem to want based on ALL sheets in the file, except the ones that seem results of other queries. I'l show you how to select the sheets to include below...

Here the final result:

I know this is giving you all columns in one table (which I beliieve is the result you should want), but below I will show you how to extract a separate table for each column...

It also includes all months between the earliest and latest EndOfMonth in you data, even if there is no data present. This complicated the query considerably, but since that was the base of your original question, I decided not to assume all months would always be present..

Here the query I used, with comments:

let
    Source = Excel.CurrentWorkbook(),  // This gives you all the tables in your workbook

    // Now select only the tables you want to include in your result
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "'Extracted Order eBay collected '!ExternalData_18" and [Name] <> "Append1" and [Name] <> "Bank_Receipts" and [Name] <> "Bank_Receipts_2" and [Name] <> "Merge1" and [Name] <> "Merge2" and [Name] <> "Slimmed_down_data")),
    
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),    // Get rid of the table names
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table.ColumnNames([Content])),  // add a column with the list of columnames in the connected sheet
    #"Unique Column Names" = List.Distinct(List.Combine(#"Added Custom"[Custom])), // combined all column names into 1 list
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", #"Unique Column Names"),  // Expand all colums found in the data
    #"All data Combined" = Table.TransformColumnTypes(#"Expanded Content",{{"End of Month", type date}}),  // the End of Month should be a date type and gave this step a descriptive name
    #"All Months occurring in the data" = #"All data Combined"[End of Month],   // this produces a list of all the End of Month dates included in the data

    // List.Generate is basically a for/each. This creates all End of Months between the minimum and maximum End of Month in the data
    #"All Months in Period List" = List.Generate(() => List.Min(#"All Months occurring in the data"),each _ <= List.Max(#"All Months occurring in the data"),each Date.EndOfMonth(Date.AddMonths(Date.StartOfMonth(_),1))),
    
    #"All Months in Period Table" = Table.FromList(#"All Months in Period List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),  // Convert the genareted list to a table
    #"End of Month Table (no data)" = Table.RenameColumns(#"All Months in Period Table",{{"Column1", "End of Month"}}),  // Give the Column in the table the correct name
    #"End of Month Table (with dummy data)" = Table.AddColumn(#"End of Month Table (no data)", #"Unique Column Names"{1}, each 0.0),  // add a dummy column named like one of the columns in the data and fill it with 0 so it will be kept during puvot and unpivot.
    #"Go back to Data Combined" = #"All data Combined",  // just to make it easier to move steps around in the UI
    #"Appended Months (with dummy data)" = Table.Combine({#"Go back to Data Combined", #"End of Month Table (with dummy data)"}), // append the dummy data to the combine data
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Appended Months (with dummy data)", {"End of Month"}, "Attribute", "Value"),   // Unpivot reduces each data columns to multiple rows
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", each List.Sum(_) ?? 0)  // Reverse the UnPivot BUT will only produce one row for each End of Month!!
in
    #"Pivoted Column"

And here your spreadsheet with the working query: 2025-02-28 WIP Sample Data Collation -Posted on Fabric Comunity.xlsx

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[PwerQueryKees]

Glad to be of help! Your call for help is of my favourite type!

You can always post a new question. And if you keep it small and to the point, you will be amazed about the number of people willing to help here!

[Rhothgar]

Just about to post another.  My biggest issue is being concise or more precisely knowing HOW to be concise with posting sample data.

The new post relates to a few tweaks to the format but with a new spreadsheet (cleaned up a little) and a couple of screenshots of the output required.

I really like your queries comments / show and tell style so I hope you will perhaps provide an answer to my next stage.

I am going to try and split out the international sales on my own.

I don't know how to explain the rest of it so I hope my sample and the screenshots will do the talking... 😁

[PwerQueryKees · Accepted solution]

OK. I gave it a try.

I downloaded your Excel and built a query to geve you the result you seem to want based on ALL sheets in the file, except the ones that seem results of other queries. I'l show you how to select the sheets to include below...

Here the final result:

I know this is giving you all columns in one table (which I beliieve is the result you should want), but below I will show you how to extract a separate table for each column...

It also includes all months between the earliest and latest EndOfMonth in you data, even if there is no data present. This complicated the query considerably, but since that was the base of your original question, I decided not to assume all months would always be present..

Here the query I used, with comments:

let
    Source = Excel.CurrentWorkbook(),  // This gives you all the tables in your workbook

    // Now select only the tables you want to include in your result
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "'Extracted Order eBay collected '!ExternalData_18" and [Name] <> "Append1" and [Name] <> "Bank_Receipts" and [Name] <> "Bank_Receipts_2" and [Name] <> "Merge1" and [Name] <> "Merge2" and [Name] <> "Slimmed_down_data")),
    
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),    // Get rid of the table names
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table.ColumnNames([Content])),  // add a column with the list of columnames in the connected sheet
    #"Unique Column Names" = List.Distinct(List.Combine(#"Added Custom"[Custom])), // combined all column names into 1 list
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", #"Unique Column Names"),  // Expand all colums found in the data
    #"All data Combined" = Table.TransformColumnTypes(#"Expanded Content",{{"End of Month", type date}}),  // the End of Month should be a date type and gave this step a descriptive name
    #"All Months occurring in the data" = #"All data Combined"[End of Month],   // this produces a list of all the End of Month dates included in the data

    // List.Generate is basically a for/each. This creates all End of Months between the minimum and maximum End of Month in the data
    #"All Months in Period List" = List.Generate(() => List.Min(#"All Months occurring in the data"),each _ <= List.Max(#"All Months occurring in the data"),each Date.EndOfMonth(Date.AddMonths(Date.StartOfMonth(_),1))),
    
    #"All Months in Period Table" = Table.FromList(#"All Months in Period List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),  // Convert the genareted list to a table
    #"End of Month Table (no data)" = Table.RenameColumns(#"All Months in Period Table",{{"Column1", "End of Month"}}),  // Give the Column in the table the correct name
    #"End of Month Table (with dummy data)" = Table.AddColumn(#"End of Month Table (no data)", #"Unique Column Names"{1}, each 0.0),  // add a dummy column named like one of the columns in the data and fill it with 0 so it will be kept during puvot and unpivot.
    #"Go back to Data Combined" = #"All data Combined",  // just to make it easier to move steps around in the UI
    #"Appended Months (with dummy data)" = Table.Combine({#"Go back to Data Combined", #"End of Month Table (with dummy data)"}), // append the dummy data to the combine data
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Appended Months (with dummy data)", {"End of Month"}, "Attribute", "Value"),   // Unpivot reduces each data columns to multiple rows
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", each List.Sum(_) ?? 0)  // Reverse the UnPivot BUT will only produce one row for each End of Month!!
in
    #"Pivoted Column"

And here your spreadsheet with the working query: 2025-02-28 WIP Sample Data Collation -Posted on Fabric Comunity.xlsx

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Rhothgar]

When I start with a blank workbook and use Get Data from Blank Query and copy and paste your code, it works.

When I open my actual spreadsheet, create a blank query and copy and paste your code it fails.  What am I doing wrong?

This is the error I get at Step 4:-

[PwerQueryKees · Accepted solution]

Sure it is on that step? I would suspect this on the "Filtered Rows" step?

If it is, then click on the step. Click the "flilter" triangle icon on the colum "Name", unselect all and then select the names of the sheets you want to include in your final result. 

[Rhothgar]

Thanks a bunch.

I'd have never worked that out. I think that probably ties back to your comments and annotations.  I didn't appreciate and hadn't fully considered that those actions need to be actioned, where necessary, in the central pane of the query editor!

Now it is added into the actual workbook, I can see that I am going to have to go through it very carefully as I have a lot of columns that are probably unnecessary as between uploading this question and your code I added additional queries and other manipulations to some of the queries.  For example, I realised that I don't need End of Month on the Payout query.  I need to see the actual dates for that.

[Rhothgar]

As an aside, if I want to transform the Payout ID column so that each cell has "Payout ID: xxxxxxxxx" where xxxxxxx equals the cell contents, what command(s) do I need to use on the ribbon please?

EDIT: Apologies. I think I just worked that one out for myself.  I used "Add Column from Examples".  Not sure if this is a dynamic and robust way of manipulating data as I am in my infancy of using power query.

[Rhothgar]

That's amazing. Thanks.

I'll take a better look at it tomorrow as it's getting late here.

I was unable to download the working query.  It said deleted or don't have permission.  I'll try again tomorrow. 

I have just copied and pasted the query into a new query to see if that works and am waiting for it to refresh whilst I type.

The annotations/REM's are a really nice touch.  I forgot you can comment out lines!  Also the little info circles against the queries with the descriptions.

[Rhothgar]

So this is the expected output I am after without using Merge or Append:-

[Rhothgar]

Does this make any more sense?

I have highlighted the cells which are missing in the Other fee data - these are the lines I am trying to create in each of the tabs where Type "postage label" and "Other Fee" do not have data in cells for intervening end of months.

BUT...  I don't want the black column.

To do this, I merged the problem queries with a query that produces all 8 rows of data from June 2024 through to Jan 2025 but by doing that it includes the Gross transaction amount column.   I solely want to edit the queries which do not have the 8 rows (or 9 rows if you include the promoted header row).

[Rhothgar]

There is a link in the above to download the sample data file.

If you need me to enter a code sample, please direct me how to do it as I can NEVER remember.

The linked file has a source file.

If I provide code sample as I think I should I just know it will give you the name of the file on my PC and not the binary encrypted link address I know I need.  

It stumps me every time!

[PwerQueryKees]

Sure, 10+ tabs, each with dozens of columns....

Guess what: I still don't have a clue what you are trying to achieve. Please isolate your issue and present only the data relevant to your issue and a sample of what you expect representative enough so a solution working on your sample will be solving your issue.

[Rhothgar]

OK.  All tabs that have a tab colour have two columns and the screenshots along with description describing what I am trying to achieve.  Please click on any tab and compare it to the screenshots and my description which I will highlight to make it stand out and that is what I am looking for.

That's the reason I spent two hours stripping out 400 lines of data to provide the sample with only 17 lines as I could not possibly anonymise 400 lines of data easily.

EDIT. I have now coloured the pertinent text in red.

[PwerQueryKees]

Please share a (sample) of the data you have and a sample of what you expect to come out of the query if applied to your sample data.