Skip to content
Chris Koester

Modern data platform and engineering in Azure

Primary Navigation Menu
Menu
  • Home
  • About
  • Contact
  • Privacy Policy

Excel

Generate a Random Alphanumeric String in Power Query

Generate a Random Alphanumeric String in Power Query

2016-04-30
By: Chris Koester
On: 2016-04-30
In: Data Integration, Power Query
With: 6 Comments

Update 2016/06/01: Bill Szysz has shared a solution in the comments below that is shorter and easier to understand than mine. He’s also shared a couple of other alternatives that seek to improve the randomness of the results. This post describes how to generate a random alphanumeric string in Power Query. This is likely not a common requirement for most Power Query users, but I saw this requirement in the Twitter API and thought it would be a fun challenge in M. Here’s the complete query. Later I’ll explain how it works.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*
Generates a single, random alphanumeric string. The string length can be modified by changing the StringLength variable.
Author - Chris Koester
https://chris.koester.io/
*/
 
let
    StringLength = 32,
    ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879",
    fnRandomCharacter = (text) => Text.Range(ValidCharacters,Int32.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1),
    GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)],
                   each [Counter] < StringLength,
                   each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)],
                   each [Character]),
    RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b)
in
    RandomString

The result of this query is a value like “kZSRd55cC67QyFWbjTXKlhnwiCttaZxU”. Every time the query is run,Read More →

JSON Array in Power Query Featured Image

Working with a JSON Array in Power Query

2016-04-15
By: Chris Koester
On: 2016-04-15
In: Data Integration, Power Query
With: 0 Comments

Update 2016-04-27:  Imke Feldmann sent me an M query that accomplishes the same goal of this post with less than half the code! The query is here. Imke’s blog is a great resource and you can also find her on Twitter. Thank you, Imke! Basic JSON structures can be parsed pretty easily by Power Query, as they often represent tabular structures that are familiar to those that work with data. Power Query simply converts the JSON to a table and you’re good to go. Working with a JSON array in Power Query, however, can be difficult and may result in duplicate rows in your dataset. JSON is builtRead More →

Follow Me

  • Twitter

Topics

Analysis Services API Azure Azure Blob Storage Azure Data Lake Store Azure Functions Azure Storage big data C# code CSV Data integration DAX Excel HDInsight Hive JSON M MDX OPENJSON ORC Parameters Power BI Power Map Power Query PowerShell REGEX Reporting Services REST SQL Server SSAS SSAS Tabular SSIS SSRS Stored Procedure Streaming text TMSL TOM TPC TPC-DS Twitter usgs VB xml

Certifications

MCSA: SQL 2016 Business Intelligence Development

Recent Posts

  • Generate Big Datasets with Hive in HDInsight
  • Delete SSAS Tabular Partitions with C#
  • Retrieve JSON Data from SQL Server using a Stored Procedure and C#
  • Load JSON into SQL Server Using a Stored Procedure and C#
  • Push Performance Counter Data into a Power BI Streaming Dataset

Categories

  • Big Data
  • Data Integration
  • Power BI
  • Power Query
  • PowerShell
  • Reporting Services
  • SSAS Tabular

Archives

  • March 2019
  • May 2018
  • March 2018
  • December 2017
  • November 2017
  • October 2017
  • August 2017
  • June 2017
  • March 2017
  • January 2017
  • May 2016
  • April 2016
  • December 2015
  • July 2015
  • August 2014

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Privacy Policy Designed using Responsive Brix. Powered by WordPress.