How to make a reproducible data sample in PowerBI using Python?

by

I’ll show you how to build a dataset of 10000 rows that contains both categorical and numerical values. I’m using the Python libraries numpy and pandas for the data generation and table operations, respectively. The snippet below simply draws a random element from two lists 10000 times to build two columns with a few street and city names, and adds a list of random numbers into the mix. Then I’m using pandas to organize the data in a dataframe. Using Python in the Power BI Power Query Editor, your input has to be a table, and your output has to be a pandas dataframe.

Python snippet:

import numpy as np
import pandas as pd

np.random.seed(123)
streets=['Broadway', 'Bowery', 'Houston Street']
cities=['New York', 'Chicago', 'Baltimore']

rows = 1000

lst_cities=np.random.choice(cities,rows).tolist()
lst_streets=np.random.choice(streets,rows).tolist()
lst_numbers= np.random.randint(low=0, high=100, size=rows).tolist()
df_dataset=pd.DataFrame({'City':lst_cities,
                      'Street':lst_streets,
                      'ID':lst_numbers})
df_metadata = pd.DataFrame([df_dataset.shape])

Power BI:

In Power BI Desktop, click Enter Data to go to the Power Query Editor. In the following dialog window, do absolutely nothing but clicking OK. The result is an empty table and two steps under Applied steps:

enter image description here

Now, use Transform > Run Python Script, insert the snippet above and click OK to get this:

enter image description here

You now have a preliminary table with 2 columns and 3 rows. And this is a pretty neat detail of the implementation of Python in Power BI. These are three different datasets that are made available to you after running your snippet. Dataset is constructed by default, but is empty since we started out with an empty table. If we started out with some other data, the first line of the Run Python Script explains the purpose of this table # 'dataset' holds the input data for this script. And it is constructed in the form of a pandas dataframe. The last table df_metadata is only a brief description of the dataset we’re really interested in: df_dataset, but I’ve added it to the mix in order to illustrate that all dataframes made by you in your snippet will be available to you. You chose which table to continue working on by clicking Table next to the name.

enter image description here

And that’s it! You now have a table of mixed datatypes to keep working on either using Python or Power BI itself:

enter image description here

From here you can:

  1. Keep working on your table using any menu option
  2. Insert another Python script
  3. Duplicate your original dataframe and keep working on another version by creating a Reference by right-clicking Table under Queries:

enter image description here

More Related Contents:

  1. How to generate random binary numbers 0 or 1 with length of N and with option to control the probability of having 0 or 1?
  2. Is there a way to ordinate numbers in a list?
  3. How do I create a list of random numbers without duplicates?
  4. Generate random integers between 0 and 9
  5. random.seed(): What does it do?
  6. How to get a random value from dictionary?
  7. Weighted random selection with and without replacement
  8. Generate random numbers summing to a predefined value
  9. Generate a random date between two other dates
  10. Generating a list of random numbers, summing to 1
  11. How can one continuously generate and track several random objects with a time delay in pygame? [duplicate]
  12. Random state (Pseudo-random number) in Scikit learn
  13. How can I randomly choose a maths operator and ask recurring maths questions with it?
  14. Read a small random sample from a big CSV file into a Python data frame
  15. Random is barely random at all?
  16. How can I create a random number that is cryptographically secure in python?
  17. Generate a random sample of points distributed on the surface of a unit sphere
  18. Random without repetition?
  19. Creating same random number sequence in Python, NumPy and R
  20. Python: Choose random line from file, then delete that line
  21. How can I generate a unique ID in Python? [duplicate]
  22. Shuffling NumPy array along a given axis
  23. scikit-learn random state in splitting dataset
  24. How to generate a random normal distribution of integers
  25. Generating non-repeating random numbers in Python
  26. Weighted random sample without replacement in python
  27. TensorFlow: Non-repeatable results
  28. Generate random locations within a triangular domain
  29. Can I generate authentic random number with python?
  30. Generate random array of floats between a range

Leave a Comment