Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online

A while back, I was working on a project involving getting data from Excel files. The Excel files contain the data in sheets and tables. Getting the data from the tables is easy. However, the data in the sheets have some commentaries on top of the sheet, then the column names and then the data itself. Something like below:

Sample data
Sample data

This approach is pretty consistent across many Excel files. The customer wants to have the commentary in the column names when the data is imported into Power BI. So the final result must look like this:

Sample Data to be loaded into Power BI
Sample Data to be loaded into Power BI

The business requirement though is to combine the first 3 rows of data and promote it as the column name.

The Challenge

Let’s connect the Excel file and look at the data in Power BI Desktop.

Connecting to sample data from Power BI Desktop
Connecting to sample data from Power BI Desktop

As

Read More

Chinese billionaire Jack Ma’s Ant Group won regulatory approval to boost the registered capital of its consumer finance unit by 10.5 billion yuan ($1.5 billion), a move suggesting progress in the fintech giant’s efforts to restructure its operations to satisfy authorities’ demands.

Chongqing Ant Consumer Finance, which was established in 2021 to host Ant’s consumer-lending businesses, such as Huabei and Jiebei, got the greenlight to increase its capital to about $2.7 billion on December 29, according to a post published by the Chongqing unit of the China Banking and Insurance Regulatory Commission. This will allow Ant to handle more consumer-oriented loans and it eliminates another regulatory obstacle en route to completing its initial public offering that was abruptly shelved in 2020. Ant was told at the time that it had to

Read More

Marketing Podcast with Steve Miller

Steve Miller, a guest on the Duct Tape Marketing PodcastIn this episode of the Duct Tape Marketing Podcast, I interview Steve Miller. Meetings & Conventions Magazine calls Steve Miller the Idea Man for his unconventional, edgy, no-spin approach to marketing and branding. He is the author of the Amazon #1 bestseller, “UNCOPYABLE: How to Create an Unfair Advantage Over Your Competition.” Steve’s speaking and consulting clients have ranged from entrepreneurs to Fortune 100 corporations, including Proctor & Gamble, Greystar Real Estate, Caterpillar, Boeing Airplane, Starbucks, Philips Electronics, and the prestigious TED Conference. We’re talking about his latest book — Stealing Genuis: The Seven Levels of Adaptive Innovation.

Key Takeaway:

Improvement is not innovation and innovation is essential if your aim is to survive in today’s business environment. Fixating your mind on improvement in today’s world is a dangerous path—one that ultimately leads to commoditization and irrelevance. In this episode, I talk with author,

Read More