Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Power Query - Creating a table from a column of data, by transposing row groupings

  • 1.  Power Query - Creating a table from a column of data, by transposing row groupings

    Bronze Contributor
    Posted 30 days ago
      |   view attached

    My primary challenge: data transformation/transpose all data stored in a single column, need to create table/matrix: specifically transposing a certain number of rows to columns, then doing the same for the next iterative "x" rows transposed to columns (in this case x=16), all stacked to create a single table.

    Using the Index column as a reference, I would eventually like to have the column of "Cell.Data.Element:Text" transformed to a table:

    Row 1 (header)  : [0:15]

    Row 2                : [16:31]

    Row 3                : [32:47]

    Row 4 ….and so on

    I hope this makes sense…

    For consideration/background: Ultimately, I will use this process to loop through a folder of similar structured files (same column headings) all to create a singular table for all the data content in the folder. Part of the wrinkle with this is that while the files created by the source have a ".xls" extension, the underlying data is actually XML, so performing a typical folder read in/input process, PBI/power query gets hung up and claims the excel file is corrupted/etc …because it's not truly Excel or CSV. My thought process is that if I can figure out one of these XML transformations, possibly I could figure out a custom function to cycle through this process for all files in the specified folder.

    When opening a source file in Excel, the headers start on row 4 and the last 8 rows are eliminated, however when using the XML data source connector in PBI, the only place I can find the content I'm looking for is in the "Row" Table: all the data I'm interested is in a single column called "Cell.Data.Element:Text".  Per my screenshots and example file, I eliminated the top unnecessary rows and introduced an index column. I thought the index might be useful, plus if I can't figure this out directly in PBI I can at least take it out of the environment keeping the order with the index column and use VBA or PY or something else to do my transpose transformations until I figure out the Power Query method.

    Also I'm grateful for any advice to my process, I'm willing to think outside the box, so any suggestions to research another method is greatly appreciated, or other resources to understanding XML.



    ------------------------------
    Brian Brown

    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 2.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Top Contributor
    Posted 30 days ago
    Can you also attach the Excel workbook or XML document that you are using as a source?
    Or paste in some sample data?

    I can't actually interact with anything in Query Editor unless I have the source doc.


    Thanks!

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Bronze Contributor
    Posted 30 days ago
      |   view attached
    Thanks for taking look. Attached is the file I used for the example.

    ------------------------------
    Brian Brown
    Operations Manager
    ------------------------------

    Attachment(s)

    xls
    MSEXP1104154515.xls   131K 1 version
    Conference-PBI_200x200


  • 4.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Top Contributor
    Posted 30 days ago
    Hi again,

    Since you are handy with VBA, why don't you run a script on the Excel file to convert it to XLSX?
    I just did a save as XLSX and was able to connect to it using the Excel connector and everything was formatted corrected.
    Just need to ditch some top and bottom rows to keep it clean.

    All the VBA (or Powershell) needs to do is open the file, then save as.
    If that is done at the time of file placement, it should be ok?

    Let me know your thoughts.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Top Contributor
    Posted 30 days ago
    Hi Brian,

    I actually just exported your data to CSV, then imported to a new power BI file.
    I had to do a manual pivot since we had to parse the rows.

    Basic steps:
    Add column for index from 1.
    Add column for modulo. This is a key piece, as it helps us break everything into sets of 16 records.
    Add a column to group by. This is created by concatenating the Index and the Modulo on the Modulo = 0 record. Then I turned blanks into null and filled up.

    Next- I added a custom column for each of the 16 columns.
    This basically mimics a SQL pivot with CASE.

    Then - I grouped everything using the group by column created earlier.

    You do have some quirks in the data in the last 3 sets (Aggregate, Average, and Benchmark).
    These sections have more than 15 columns in them!
    I deleted those records from the CSV, just FYI.
    I could have removed some rows in Power BI but I wasn't sure why those three sections
    Bottom line: all the groups need to have the same number of columns.

    There may be a better way to do this, of course.
    Transpose and Unpivot were a bit awkward and I couldn't get them to work quite the way I wanted to, but I feel that it should be possible using those transforms.

    Anyway - let me know if you have questions.

    Next  - I will use your real source doc and see if I can use ParseXML to any great affect.


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Attachment(s)

    csv
    data.csv   23K 1 version
    Conference-PBI_200x200


  • 6.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Top Contributor
    Posted 30 days ago
    Edited by Audrey Abbey 30 days ago
    Brian,

    I have a MUCH easier solution for you.

    See attached.

    What this does is creates the grouping column with modulo like before.
    Then you can jump straight to a GROUP BY.
    We pick a default aggregation (max).
    Then edit the query via advanced query editor and replace the Max with a Text.Combine.

    Replace this piece of code:
    each List.Max([Element]), type text}})

    With this piece:
    each Text.Combine([Element],",")}}),

    What this does is create a comma separated list of the values for each group #.
    From there, all you have to do is split the column on the comma, and promote your first row as a header.

    If you have commas in your data, you can replace the comma with a pipe (|), then split on the pipe.

    Boom done.
    I had forgotten about the concatenation trick. It is VERY helpful. ​

    Edited to Add: I realized I forgot to attach the file and I can't figure out how to do in on an edit.
    I will post again. Sorry for the trouble.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Top Contributor
    Posted 30 days ago
      |   view attached
    See attached for the example using the concatenate string.

    Let me know if you have any questions.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 8.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Bronze Contributor
    Posted 29 days ago
    Thanks! This is great!  I'm going to need to take a bit to really go through it, but I follow the logic.  I was also thinking about using modulo with the index, but as you pointed out I do need to start with 1, but hadn't thought it through.  I'm not great at VBA, but I do have a snippet that I use to batch convert .xls to .csv, I can easily adjust to .xlsx.  I did try the batch .csv convert but was coming up with the same error as .xls, I'll try the .xlsx batch conversion and let you know if it works (I'm assuming it will).

    I was getting hung up on trying to do the transpose/pivot in PBI, I feels like it should be easier, but ultimately it is awkward.  I do know enough VBA that I can do the transpose/pivot thing, however I was trying to avoid that if possible as I'm trying to build this unit so that a colleague (with no coding experience) can also use it going forward. Putting data into PBI, export, transform, then re import, would be fine for me, but the process would definitely get lost on some of the potential users.

    As far as eliminating the aggregate/average/benchmark rows, that's exactly what I will do.

    However your most recent post, seems elegant and repeatable, I look forward to testing it. I'll report back... thanks again for your efforts and sharing your thought process, definitely very helpful.



    ------------------------------
    Brian Brown
    Operations Manager
    ------------------------------

    Conference-PBI_200x200


  • 9.  RE: Power Query - Creating a table from a column of data, by transposing row groupings

    Bronze Contributor
    Posted 26 days ago

    There are so many good nuggets I took away from this. First off, I wasn't aware you can do a modulo transform within M/Power Query. Normally I would have extracted out of PBI and performed this in Excel/Py, then re-imported the transformed data. In addition - the fill-up step, and the grouping trick with concatenation; I wish I knew about that sooner too. I will likely be using all of these in the future, as my goal going forward is to try to contain as much repeatable process within PBI as I know how.

    Part of my initial steps which led to these transform processes was because I could not get my VBA batch file/type conversion to work in a way that PBI could read-in a group of files with the original layout or a close approximation.  I was having trouble with my batch conversion snippet I had used in the past, and was looking for a modification, or alternate way. I found this forum thread to be particularly useful:

     

    https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1206178-change-400-files-from-xlsb-to-xlsx

     

    of course, I had to change a few things, this link will help others that need to transform other file types, just swap out 'target' and 'save as' file type references:

     

    https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

     

    While not technically part of the original thread title, the final step in the process I previously outlined; I wanted to batch read-in multiple files of a similar layout and stack in a single table.  I have tried this with the .xlsx converted files and .csv converted files, the .csv files seem to work better, or at least at first try there were less steps involved. With regard to the 'buffer' rows, I was able to clean/eliminate the top (3) and bottom (8) rows in the 'transform sample file' steps in the query editor (created when selecting the folder input) so that those steps are applied to all files in the folder I'm reading-in.

     

    I can not thank you enough for educating me, and helping me educate myself.



    ------------------------------
    Brian Brown
    Operations Manager
    ------------------------------

    Conference-PBI_200x200