Putting columns on top of each other with one column being common

Hi all,
Hoping someone would be able to help with this. :smile: Will need to do this regularly, so hoping to not have to do it manually every time. I have many of these so trying to automate the clean up and preps as much as possible. Could I put columns on top of each other where the date column is the same. Here is the example and and what i am trying to achieve. Any help would be really appreciated.
Date Type Number Type2 Number
01/05/2019 A 2 B 2
02/05/2019 A 3 B 4
03/05/2019 A 4 B 24
04/05/2019 A 2 B 2
05/05/2019 A 3 B 4

And here is what i would like it to look like (i attached the excel as well)

Thank you so much in advance for any help.

Example.xlsx (10.4 KB)


Can you attach the xlsx again?

Thanks so much, think attachment worked now?

Yep, thanks

Give this a try.
Note that if you update the FileLocation Parameter all queries will be restored.

eDNA - Putting columns on top of each other.pbix (31.3 KB)
Example.xlsx (11.6 KB)

Hey Melissa,

Thank you so much for helping out.

Sadly, it says its unable to open the document as the “queries were authored with a newer version of Power BI desktop and might not work on your version”
I will upgrade next week when back in the office. But could you possibly share the high level steps you did of how you manage to work it out?

I also tried changing the file location but got error “Key didn’t match rows in the table”.

Thank you so much in advance for your help,


Sure no worries.

I created a tRegion table

Based on the xlsx from my previous post, here’s the M code for that:

    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    tRegion_Table = Source{[Item="tRegion",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tRegion_Table,{{"Type", type text}, {"Region", type text}})
    #"Changed Type"


and this is the result for the tData table


M code for that query:

    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    tData_Table = Source{[Item="tData",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tData_Table,{{"Date", type date}, {"Type", type text}, {"Number", Int64.Type}, {"Type2", type text}, {"Number ", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Number", type text}}, "nl-NL"),{"Type", "Number"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Number ", type text}}, "nl-NL"),{"Type2", "Number "},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByPositions({0, 1}, false), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Value.1"}, tRegion, {"Type"}, "tRegion", JoinKind.LeftOuter),
    #"Expanded tRegion" = Table.ExpandTableColumn(#"Merged Queries", "tRegion", {"Region"}, {"Region"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded tRegion",{{"Value.1", "Type"}, {"Value.2", "Number"}})
    #"Renamed Columns"

Might I suggest creating a Parameter for the source location?

As you can see in all M codes ‘source’ lines above I use a Parameter to hold the file/source location.
I think it’s a best practice because when a location changes I only have to update this Parameter to restore all my queries.

I hope this is helpful.

1 Like

Oh brilliant! thank you so much. Gonna give it a go and let you know if i made it work.

Thanks a million!

1 Like

Melissa, im really sorry, dont think i am that advanced in power bi to replicate what you managed to do in minutes. :slight_smile: i tried… If not too annoying would you be able to tell me how you transformed the columns into the table im trying to achieve. :slight_smile: please. I can see you unpivoted columns? sorry once again…

No problem Erika, get ready - here we go:

Select Type & Number, go to Transform and Merge Columns


Select Date and Unpivot other Columns

Right click and Remove the Attribute column

Select Value and Split Collumn by Position

On the Home tab go to Merge Queries

Merge tData with tRegion

Expand and only select Region

and you’re done!

1 Like

OMG, genius, and totally works in my original file! Love it. Thank you so much. Have a nice weekend.