Table merging

yeders
Posts: 3
Joined: Tue Jul 04, 2017 4:11 pm

Table merging

Postby yeders » Tue Jul 04, 2017 4:20 pm

Hi guys,

New to the forum and relatively new to powerquery.

I have a powerquery where I have merged 5 spreadsheets together.

One column (column A) generally contains common/duplicating data, the adjacent columns contains a matrix of unique column headers (25 unique columns), and each row of these columns indicates if the data in column A is used by that person.

If the data wasn't so large, i could use index & match as suggested here: https://superuser.com/questions/366647/ ... mon-column

But given its across multiple sheets it is relatively cumbersome to do this.

The attached picture gives an example of what it looks like to begin with after the power query merger and demonstrates what I would want the end product to look like.

Any suggestions?

thanks
Attachments
example.PNG
Example
example.PNG (14.88 KiB) Viewed 4514 times
Last edited by yeders on Tue Jul 04, 2017 5:00 pm, edited 1 time in total.

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Table merging

Postby MattAllington » Tue Jul 04, 2017 4:25 pm

so each column is a name of a person? What do you want to do with this information? Maybe unpivot it? Select the first column, right click and select "Unpivot other columns"

I'm not sure if that is what you are after
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

yeders
Posts: 3
Joined: Tue Jul 04, 2017 4:11 pm

Re: Table merging

Postby yeders » Tue Jul 04, 2017 5:13 pm

Hi thanks for your reply. I have edited the original to include a picture/example of what I'm trying to achieve.

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Table merging

Postby MattAllington » Tue Jul 04, 2017 5:18 pm

You need to unpivot the data first (as I explained above), then repivot it again. When you re-pivot, select "Do not Aggregate". It should work
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

yeders
Posts: 3
Joined: Tue Jul 04, 2017 4:11 pm

Re: Table merging

Postby yeders » Tue Jul 04, 2017 5:40 pm

Hi Matt,

When I repivot I get:

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type

There are multiple columns = null

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Table merging

Postby MattAllington » Tue Jul 04, 2017 7:23 pm

I would need to see the workbook to help from here. Can you post a sample with the same issue?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Rudi
Posts: 6
Joined: Fri Sep 01, 2017 4:33 pm

Re: Table merging

Postby Rudi » Fri Sep 01, 2017 4:51 pm

Attachments
Yeders.xlsx
(19.91 KiB) Downloaded 132 times

User avatar
AndVGri
Posts: 31
Joined: Thu Jun 02, 2016 2:41 am
Location: Sankt-Peterburg

Re: Table merging

Postby AndVGri » Sun Sep 03, 2017 10:28 pm


Rudi
Posts: 6
Joined: Fri Sep 01, 2017 4:33 pm

Re: Table merging

Postby Rudi » Mon Sep 04, 2017 2:48 pm

I agree completely that Matt's method is better and more optimal. His method worked for me the first time round but the only reason why I posted a different method is that the OP mentioned they were getting errors so maybe a different approach could have help them.

Cheers :)


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests