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 2809 times
Last edited by yeders on Tue Jul 04, 2017 5:00 pm, edited 1 time in total.

MattAllington
Posts: 946
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".
https://exceleratorbi.com.au/power-bi-online-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: 946
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".
https://exceleratorbi.com.au/power-bi-online-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: 946
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".
https://exceleratorbi.com.au/power-bi-online-training/

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

Re: Table merging

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

Hi Yeders,

This is an old thread, but for the sake of others reviewing it and if the rare event that it's still not resolved :o

Try this M-Code:

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Column", type text}, {"Area 1", type text}, {"Area 2", type text}, {"Area 3", type text}, {"Area 4", type text}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Area 1", "Area 2", "Area 3", "Area 4"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Up", "Index", 1, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Inserted Modulo", each Number.Mod([Index], 4), type number),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Inserted Modulo] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Inserted Modulo"})
in
    #"Removed Columns"

Sample workbook attached...

Yeders.xlsx
(19.91 KiB) Downloaded 81 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

Hi
I think in that case Matt's algorithm is better. To do unpivot and after to do pivot again.

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Unique Column"}, "Attribute", "Value"),
    attrList = List.Distinct(Unpivoted[Attribute]),
    result = Table.Pivot(Unpivoted, attrList, "Attribute", "Value")
in
    result

Regards,

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 2 guests