Combining X Variety of Rows in Energy Question for Energy BI, Excel and Energy Question On-line

0
80
Combining X Variety of Rows in Energy Question for Energy BI, Excel and Energy Question On-line


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

Some time again, I used to be engaged on a mission involving getting information from Excel recordsdata. The Excel recordsdata include the information in sheets and tables. Getting the information from the tables is straightforward. Nonetheless, the information within the sheets have some commentaries on prime of the sheet, then the column names after which the information itself. One thing like under:

Sample data
Pattern information

This strategy is fairly constant throughout many Excel recordsdata. The client needs to have the commentary within the column names when the information is imported into Energy BI. So the ultimate end result should appear to be this:

Sample Data to be loaded into Power BI
Pattern Information to be loaded into Energy BI

The enterprise requirement although is to mix the primary 3 rows of knowledge and put it on the market because the column identify.

The Problem

Let’s join the Excel file and have a look at the information in Energy BI Desktop.

Connecting to sample data from Power BI Desktop
Connecting to pattern information from Energy BI Desktop

As you possibly can see within the previous picture, Energy BI, or extra exactly, Energy Question, sees the information in Desk format. After we click on the Rework Information button, that is what we get in Energy Question Editor:

Connected to sample data from Power Query in Power BI Desktop
Related to pattern information from Energy Question in Energy BI Desktop

Everyone knows that tables encompass Columns and Rows. The conjunction of a column and a row is a Cell. What we require to do is to concatenate the values of cells from the primary three rows. We even have to make use of a Area character to separate the values of every cell from the others.

Column, rows and cells in a Table in Power BI
Column, rows and cells in a Desk

In Energy Question, we will get every row of knowledge in as a Report with the next syntax:

Desk{RecordIndex}

Within the above syntax, the Desk could be the outcomes of the earlier transformation step, and the RecordIndex begins from 0. So to get the primary row of the desk within the previous picture, we use the next syntax:

#"Modified Sort"{0}

The place the #"Modified Sort" is the earlier step. Listed below are the outcomes of operating the previous expression:

Getting the first row of a Table
Getting the primary row of a Desk

So we will get the second and third rows with comparable expressions. The next picture exhibits your entire codes within the Superior Editor:

Power Query expressions in Advanced Editor in Power BI Desktop
Energy Question expressions in Superior Editor

However how will we concatenate the values of the rows?

My strategy is:

  • Making a desk from the primary three rows
  • Transposing the resutls to get the rows as columns
  • Merging the columns right into a single column
  • Transposing the brand new column
  • Appending the transposed values to the unique desk
  • Selling the primary row as column names

Right here is how we accomplish that:

  1. We create a Desk from Data utilizing the Desk.FromRecords() perform:
Creating a Table from Records
Making a Desk from Data
  1. Transposing the resutls to get the rows as columns
Transposing columns in Power Query
Transposing columns in Energy Question
  1. Merging all columns and eradicating the columns. To take action we choose all columns, right-click a specific column and click on Merge Columns. We use Area because the separator
Merging columns as a new column in Power Query
Merging columns as a brand new column in Energy Question
  1. Transposing the brand new merged column
Transposing a column in Power Query
Transposing a column in Energy Question
  1. Appending the transposed outcomes to the unique desk. The resutls of the third transformation step provides us the orignial desk. We use the Desk.Mix() perform
Desk.Mix({#"Transposed Table1", #"Modified Sort"})
Appending the transposed results to the original data in Power Query
Appending the transposed outcomes to the unique information

As you possibly can within the previous picture, the outcomes are usually not what we would like as we nonetheless have the primary 3 rows showing within the values. So earlier than we append the transposed outcomes to the unique desk, now we have to take away the primary 3 rows from the unique desk, after which append the outcomes. To take away the primary N rows we use Desk.Skip() perform. So now we have to alter the earlier step as follows:

Desk.Mix({#"Transposed Table1", Desk.Skip(#"Modified Sort", 3)})
 Appending the transposed results to the results of removing the first 3 rows of the original data
Appending the transposed outcomes to the outcomes of eradicating the primary 3 rows of the unique information
  1. Selling the primary row as column names
Promoting the first row as column headers in Power Query
Selling the primary row as column headers in Energy Question

Simple!

Hmm… Probably not. Particularly when now we have a number of different Excel recordsdata; some have two, some have three rows of feedback, and a few have much more. So not solely is the duty time-consuming, however it is also fairly boring and susceptible to human errors. What if there’s a customized perform that I can invoke to get the job carried out?

The Answer

To beat the problem, I wrote a reasonably dynamic customized perform. It accepts a desk and an iterator. The iterator is the variety of rows we wish to concatenate and use because the column names. After invoking the perform, it iterated by way of the primary X variety of rows following the state of affairs I defined within the earlier part. It then appends the transposed outcomes to the unique desk whereas eradicating the primary X variety of rows.

Right here you go…

Once I examined my unique perform with totally different information varieties, I famous that I needed to convert the remark values to textual content information sort; in any other case, dynamically, the merging columns step breaks because it can’t concatenate textual content and quantity values.

An enormous shout-out to Imke Feldman for writing such useful blogposts. I used her approach in a single block of the next code to dynamically convert the columns to textual content.

// fn_CombineTopXRows
(InputTable as desk, Iterator as quantity) as desk =>
let
  TopXRows = Listing.Generate(
        () => 0
        , every _ < Iterator
        , every _ + 1
        , every InputTable{_}
        ),
    TableFromRecords = Desk.FromRecords(
        Listing.Generate(
            () => 0
            , every _ < Iterator
            , every _ + 1
            , every InputTable{_}
            )
        ),
    TransposeTable = Desk.Transpose(
        TableFromRecords
        , Listing.Generate(
            () => 0
            , every _ < Iterator
            , every _ + 1
            , every "Col" & Textual content.From(_)
            )
        ),
    
    // Particular because of Imke Feldman for this line of code?
    // https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m/
    DataTypeToText = Desk.TransformColumnTypes(
        TransposeTable
        , Listing.Rework(
            Desk.ColumnNames(TransposeTable)
            , every {_, sort textual content}
            )
        ),
    
    CombineColumns = Desk.CombineColumns(
        DataTypeToText
        , Listing.Generate(
            () => 0
            , every _ < Iterator
            , every _ + 1
            , every "Col" & Textual content.From(_)
            )
        , Combiner.CombineTextByDelimiter(
            " "
            , QuoteStyle.None
            )
        ,"Merged"
        ),
    TransposeCombined = Desk.Transpose(CombineColumns),
    AppendToOriginalTable = Desk.Mix({TransposeCombined, Desk.Skip(InputTable, Iterator)}),
    PromotedHeaders = Desk.PromoteHeaders(AppendToOriginalTable, [PromoteAllScalars=true])
in
    PromotedHeaders

You may obtain the code from right here.

Let’s see the fn_CombineTopXRows customized perform in motion. Listed below are the outcomes after invoking the perform with our earlier pattern information:

Invoking the fn_CombineTopXRows customized perform

Here’s a extra excessive pattern information containing particular characters, nulls and Unicode characters:

Pattern information

And listed below are the outcomes of invoking the fn_CombineTopXRows customized perform:

Invoking the fn_CombineTopXRows customized perform

As at all times, if in case you have any feedback or you understand a greater method than I defined right here, please let me know by way of the feedback part under.

Replace

After sharing this submit on LinkedIn, Muneer Marzouq, commented that he would write the customized perform in another way. I appreciated his strategy, so I share his code right here.

// fn_CombineTopNRows
// Supply: https://www.linkedin.com/feed/replace/urn:li:exercise:6866137870068002816?commentUrn=urnpercent3Alipercent3Acommentpercent3Apercent28activitypercent3A6866137870068002816percent2C6866640507234254848percent29&replyUrn=urnpercent3Alipercent3Acommentpercent3Apercent28activitypercent3A6866137870068002816percent2C6867174468557529088percent29

(InputTable as desk, TopNRows as quantity, elective Delimiter as nullable textual content) as desk =>
let
  Delimiter = if Delimiter <> null then Delimiter else "#(lf)",
  ToTables = Desk.Cut up(InputTable, TopNRows),
  ColsNames = Desk.ColumnNames(ToTables{0}),
  ToText = 
    Desk.TransformColumnTypes(
      ToTables{0}
      , Listing.Rework(
          ColsNames
          , every {_, sort textual content}
        )
    ),
  ToColumns = Desk.ToColumns(ToText),
  CombineText = 
    Listing.Rework(
      ToColumns
      , every Textual content.Mix(_
        , Delimiter
      )
    ),
  ToTable = Desk.FromRows({CombineText}, ColsNames),
  Mix = Desk.Mix({ToTable, ToTables{1}})
in
  Mix

Replace 2022 Jan

Right here is one other answer that Mahmoud Baniasadi put within the feedback part. What I like about Mahmoud’s code are:

  • Utilizing the Desk.SplitAt() perform which returns an inventory of two tables as a substitute of Desk.Cut up() perform which returns an inventory of tables
  • Implementing the perform documentation
let func=
(InputTable as desk, TopNRows as quantity, elective Delimiter as nullable textual content) as desk =>
let

Delimiter = if Delimiter <> null then Delimiter else "#(lf)",
// Correction: Change Desk.Cut up to Desk.SplitAt
ToTables = Desk.SplitAt(InputTable, TopNRows),
ColsNames=Desk.ColumnNames(ToTables{0}),
ToText= Desk.TransformColumnTypes(
ToTables{0}
, Listing.Rework(
ColsNames
, every {_, sort textual content}
)
),

/* My different technique for this half
CombineText=
Listing.Rework(
Desk.ToList(
Desk.Transpose(ToText),
Combiner.CombineTextByDelimiter(Delimiter,0)),
every Textual content.Trim(_)),
*/

// Muneer Marzouq Answer
ToColumns=Desk.ToColumns(ToText),
CombineText =
Listing.Rework(
ToColumns
, every Textual content.Mix(_
, Delimiter
)
),
//Finish of Muneer code which is totally different from mine

ToTable = Desk.FromRows({CombineText}, ColsNames),
Mix = Desk.Mix({ToTable, ToTables{1}}),
// Correction: add one step to advertise mixed rows as header
PromotedHeaders = Desk.PromoteHeaders(Mix, [PromoteAllScalars=true])
in
PromotedHeaders,
// Add documentation
documentation = [
Documentation.Name = " fn_CombineTopNRows ",
Documentation.Description = " Returns a Table with Combining X Number of Rows. ",
Documentation.LongDescription = " Returns a Table with Combining X Number of Rows and Promote them as header. ",
Documentation.Category = " Table ",
Documentation.Source = " https://www.biinsight.com/combining-x-number-of-rows-in-power-query-for-power-bi-excel-and-power-query-online/ ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Muneer Marzouq, Mahmoud Bani Asadi, Soheil Bakhshi ",
Documentation.Examples = {[Description = " see this blogpost: https://www.biinsight.com/combining-x-number-of-rows-in-power-query-for-power-bi-excel-and-power-query-online/ ",
Code = " fn_CombineTopNRows(#table({""Column1"", ""Column2"", ""Column3""}, {{""TitleA"", ""TitleC"", ""Title1""}, {""TitleB"", ""TitleD"", ""Title2""}, {7, 8, 9}}), 2, "" "")",
Result = " #table({""TitleA TitleB"", ""TitleC TitleD"", ""Title1 Title2""}, {{7, 8, 9}}) "]}]
in
Worth.ReplaceType(func, Worth.ReplaceMetadata(Worth.Sort(func), documentation))

Because of this I really like data sharing. You study one thing new from others day by day.



Supply hyperlink

LEAVE A REPLY

Please enter your comment!
Please enter your name here