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

Jetuke_ES · March 6, 2020, 9:12pm

Hi all,
Hoping someone would be able to help with this. 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.
APAC EMEA
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)
|DATE|Region|Type|Number|
|01/05/2019|APAC|A|2|
|02/05/2019|APAC|A|3|
|03/05/2019|APAC|A|4|
|04/05/2019|APAC|A|2|
|05/05/2019|APAC|A|3|
|01/05/2019|EMEA|B|2|
|02/05/2019|EMEA|B|4|
|03/05/2019|EMEA|B|24|
|04/05/2019|EMEA|B|2|
|05/05/2019|EMEA|B|4|

Thank you so much in advance for any help.

Example.xlsx (10.4 KB)

Melissa · March 6, 2020, 9:24pm

@Jetuke_ES,

Can you attach the xlsx again?
Thanks

Jetuke_ES · March 6, 2020, 9:37pm

Thanks so much, think attachment worked now?

Melissa · March 6, 2020, 9:37pm

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)

Jetuke_ES · March 7, 2020, 4:13pm

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,

erika

Melissa · March 7, 2020, 4:31pm

Sure no worries.

I created a tRegion table

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

let
    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}})
in
    #"Changed Type"

.

and this is the result for the tData table

M code for that query:

let
    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"}})
in
    #"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.

Jetuke_ES · March 7, 2020, 4:47pm

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

Thanks a million!
erika

Jetuke_ES · March 7, 2020, 7:29pm

Melissa, im really sorry, dont think i am that advanced in power bi to replicate what you managed to do in minutes. 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. please. I can see you unpivoted columns? sorry once again…