How to Create Reusable Power BI Sample Data Using ...

KarinSzilagyi · ‎11-10-2025

Have you ever tried explaining a Power BI issue and immediately realized you can’t share the dataset behind it?

Maybe the data is confidential, maybe it’s messy, or maybe you just don’t want to hand-craft a new example every time. Before you know it, you’re exchanging files, fixing paths, and losing momentum...

My solution? Generate a sample dataset using Power Query only!

There are several ways to do this, but in this post, I’ll walk you through my favourite method which is easy to adapt to your own use case. It’s simple, reusable, and easy to tweak, even if you are not fluent in M.

Note: More efficient solutions exist if you are fluent in M, but this guide focuses on a beginner-friendly approach that anyone can follow.

Step 1: Start with a Blank Report

Begin by creating a small placeholder table via Home > Enter Data. This so-called “Seed Table” only needs one column and one row - just enough to get started. You can name the column anything you like (e.g., Column1) and enter a simple value like 1, A, or null.

In the image above, I used the value "A". The table must contain at least one row of data otherwise the following steps will not work properly. I have also given my new table a name – Fact_Sample – but you can use any name you like.

Next, open the Power Query editor by selecting Edit instead of Load. Your table should look like the image below:

If your table does not look like this and appears to have no data, as shown in the next image, it means you forgot to enter a value in Column1. Go back and edit the seed table by selecting the gear icon at the Source step and add any value to Column1.

Step 2: Generate Rows

In the Power Query editor, go to Add Column > Custom Column, name the new column (e.g., ID) and enter

{1..150}

into the Custom column formula field.

This creates a continuous list of numbers from 1 to 150.

Expand this list using Expand to New Rows, then change the ID column type to Whole Number and delete the initial placeholder column (Column1).

Step 3: Add a Value Column

Add another custom column, assign a name (for example Value) and enter the following formula in the Custom column formula field.

Number.RandomBetween(1000, 20000)

This generates a random number between 1000 and 20,000 on each refresh of the dataset. You can format it as a Decimal Number or Whole Number, and round it if needed.

The first number (1000) is your lowest possible value, and the second number is your highest possible value.

You can change the data type to Decimal Number or Fixed Decimal Number for floating values or Whole Number if you prefer integers.

Now that we have some random values to use in our calculations, let’s add our Date column. We will use a similar method as we previously used to create our Values, but with an important twist.

Step 4: Add a Date Column

Power BI stores dates internally as numbers starting from 30th December 30, 1899 (which equates to 0). This quirk stems from Excel’s compatibility with another popular spreadsheet program from the 1980s, Lotus 1-2-3, which mistakenly treated 1900 as a leap year.

When Microsoft built Excel, they wanted to be fully compatible with Lotus files, so they intentionally copied the same date system – including the bug.

Later versions of the Windows operating system standardized the calendar for all Microsoft apps and shifted the base back one more day, making 30th December 1899, the official day 0. Without that “imaginary leap day”, Excel’s (and Power BI’s) “day zero” would have been December 31st, 1899, instead.

Now that we know that a date can be represented as a number by counting the days since 30th December 1899, we can test this behaviour by adding another custom column with any date value of your choice (this step is optional).

Make sure that the data type of your column is first set to Date, then change it to Whole Number:

Try it with any dates of your choice!

We can use this knowledge to generate random dates between any selected start and end dates by first translating them to their numeric value, then applying Number.RandomBetween() to generate a random number between our start and end date serial and finally changing the type of our resulting values back to Date.

First, let’s delete the DateExample column from above (if you created one) - we only needed it to visualize the concept.

Now, we will add new custom column via Add Column > Custom Column and enter the following formula:

Number.RandomBetween(
    Number.From(#date(2024,1,1)), 
    Number.From(#date(2025,12,31))
)

Number.RandomBetween() – generates a random number between two bounds.
Number.From() – converts a string or date to a number; used twice here to define the minimum and maximum numbers for our Number.RandomBetween().
#date(yyyy, m, d) – creates a valid, locale-independent date from date-parts; used twice here for our start and end dates which we then transform to numbers in Number.From().

Note: You can use any expression that returns a valid Date inside Number.From(), for example, Date.From(“2024-01-01”). I recommend that you define dates with the locale-independent #date(y, m, d) literal to avoid issues with regional formats.

After adding the column, change the data type to Date:

Step 5: Add a Dimension Key

We can use the same approach (Add Column > Custom Column + Number.RandomBetween()) to generate dimension keys for any dimension table you need. For this exercise, we will add a CountryID which can range from 1 - 10:

Number.RandomBetween(1,10)

Change the data type of our new column to Whole Number:

Finally, add the matching dimension table manually via Home > Enter Data:

You could use a similar method as we used for our Fact_Sample to create a Date dimension by generating a list from the numeric values of your start and end date, converting it to dates, and adding more columns through the Power Query date functions.

But there’s an even cleaner and more readable way to do it: Power Query’s List.Dates() function was built to directly generate a continuous range of dates between two points. You can reuse this script as the basis for any future date tables by simply adjusting the StartDate and EndDate:

let
    Source_StartDate = #date(2024, 1, 1),
    Source_EndDate = #date(2025, 12, 31), 
    Duration_Days = Number.From(Source_EndDate) - Number.From(Source_StartDate) + 1, 
    DateList = List.Dates(Source_StartDate, Duration_Days, #duration(1,0,0,0)), 
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type - Date" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added Year" = Table.AddColumn(#"Changed Type - Date", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Added Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Inserted Month", "Month Name (EN)", each Date.MonthName([Date], "en-US"), type text),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Month Name", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Days in Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Added Day Name" = Table.AddColumn(#"Inserted End of Quarter", "Day Name (EN)", each Date.DayOfWeekName([Date], "en-US"), type text)
in
    #"Added Day Name"

Navigate to Home > New Source > Blank Query, then select Home > Advanced Editor and paste the full code snippet into the editor. Adjust the Source_StartDate and Source_EndDate as needed and click Done.

You can also change the column formatting and locale (for example, to display month or day names in different languages):

Summary

That’s it! You now have a working dataset with a fact table, a date dimension and a sample dimension – all created using Power Query alone.

Load the data via Home > Close & Apply and set up the relationships in your data model:

This method is great for quickly creating data to test Measures, share examples in the Power BI Community, or experiment with Power Query functions without requiring any external files.

Once you have built this dataset, you can reuse it for many different scenarios. Just change the number of rows, date ranges, and adjust the columns as needed to generate a fresh dataset on demand.