Data Table Transformations Question

[Anonymous]

Hi All, 

I have a table structured like this:

		Currency type	EUR
		Fiscal year	2016
		Version	100
		Period	1
Category	Country	Country Capital	Value A	Value B

And I am trying to get the following Colums:

Category, Country Country Capital Value A and Value B.

Currency, Fical Year Version and Period Should be Attributes of the above columns, somehow however I am unable to Transform the data as such. 

Everytime I Transpose to have this Setup:

		Category
		Country	2016
		Country Capital	100
		Value A Value B	1
Currency type	Fiscal year	Version	Period

Then I unpivot All coumns but Currency Type, Fiscal Year, Version and Period I get the two value coulms I want but I am loosing the attributes. Hope I was able to describe the issue properly.

Kr,

Mike

[MarcelBeug]

To me, it is totally unclear.

Do you mean you have some input with Currency type, fiscal year, version and Period as headers and Category, Country, Country Capitial, Value A and Value B with data rows underneath?

So are there data rows underneath your bottom lables (Category thru Value B)?

Is that data repeating, so you have a set headers followed by a set of data rows, then a header followed by a set of data rows etcetera?

I have no ideas what you mean with "should be attributes of the above columns".

Can you provide a more complete example of your input with corresponding output how it should look like?
I would expect that the result should be some flat table with all data from headers and data rows combined on 1 row, so you get rows with each 9 columns?

Just to be sure: are you tallking about tables as in Power Query and the data model, or as table visual?

[MarcelBeug · Accepted solution]

This is my interpretation.

Query Step1:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Data Table Transformation Question.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Column3] = "Currency type" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"})
in
    #"Filled Down"

Query Headers:

let
    Source = Step1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column3", "Column4", "Group"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Column3]), "Column3", "Column4")
in
    #"Pivoted Column"

Query Result:

let
    Source = Step1,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"0", "Group"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Category] <> "Category")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Group"},Headers,{"Group"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Currency type", "Fiscal year", "Version", "Period"}, {"Currency type", "Fiscal year", "Version", "Period"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded NewColumn",{"Group"})
in
    #"Removed Columns1"

[Anonymous]

Dear Marcel, 

Thank very much for the practical example and the video tutorial. 

After experimenting further in Power Query and looking at your code vs. what I had I have figured out how to change my Matrix Table into a Columnar Variant. 

In the end I merged the Dimensions I had in the Rows into one single text String for each Combination, after transposing one more time and Unpivoting all of these columns I had the additional dimensions as a column and could split them out into colums again. 

With kind regards,

Mike

[Anonymous]

Dear Marcel, 

Thanks for your answer, in your video you show exactly the end result that I want. 

Let me give you a better table filled with some more Data. 

		Currency type	EUR	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
		Fiscal year	2016	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
		Version	100	100	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	200	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
		Posting period	1	1	null	2	3	null	4	null	5	null	6	null	7	null	8	null	9	null	10	null	11	null	12	null	1	null	2	null	3	null	4	null	5	null	6	null	7	null	8	null	9	null	10	null	11	null	12	null
Category	Country	Counrty Capital	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B	Value A	Value B
Bikes	Europe	Brussels	-31	918	-133	871	143	759	66	615	15	592	21	572	-4	-133	871	143	562	-32	550	20	729	10	857	-16	977	111	550	20	729	10	857	-16	977	111	961	-40	729	10	857	-16	977	111	961	-40	916	-135	-16	977
null	UK	London																	-112	-112	-112	-112	-112	-112	-143	-143	-143	-143	-112	-112	-112	-112	-143	-143	-143	-143	-143	-143	-112	-112	-143	-143	-143	-143	-143	-143	-143	-143	-143	-143
null	France	Paris	38	793	-2	806	75	767	148	618	-13	570	30	705	-47	-2	806	75	466	-45	518	24	634	3	705	-58	732	19	518	24	634	3	705	-58	732	19	822	32	634	3	705	-58	732	19	822	32	806	11	-58	732
null	null	null	95	1,298	-73	1,390	41	1,148	-72	1,123	9	1,198	-56	1,417	-80	-73	1,390	41	665	-27	878	26	1,218	47	1,129	-43	1,341	80	878	26	1,218	47	1,129	-43	1,341	80	1,331	80	1,218	47	1,129	-43	1,341	80	1,331	80	1,317	-49	-43	1,341
Flowers	Europe	Brussels	-31	918	-133	871	143	759	66	615	15	592	21	572	-4	-133	871	143	562	-32	550	20	729	10	857	-16	977	111	550	20	729	10	857	-16	977	111	961	-40	729	10	857	-16	977	111	961	-40	916	-135	-16	977
null	UK	London	46	1,207	-90	1,220	128	959	66	907	-15	682	40	796	-55	-90	1,220	128	636	-20	669	37	944	50	893	-3	1,084	115	669	37	944	50	893	-3	1,084	115	1,147	30	944	50	893	-3	1,084	115	1,147	30	1,186	-112	-3	1,084
null	France	Paris	34	793	6	806	75	767	148	618	-13	570	30	705	-47	6	806	75	466	-47	518	24	634	2	705	-58	732	17	518	24	634	2	705	-58	732	17	822	30	634	2	705	-58	732	17	822	30	806	19	-58	732
null	null	null	38	793	-2	806	75	767	148	618	-13	570	30	705	-47	-2	806	75	466	-45	518	24	634	3	705	-58	732	19	518	24	634	3	705	-58	732	19	822	32	634	3	705	-58	732	19	822	32	806	11	-58	732

In the meantime I will look through your query steps, to see if that already gives me the required result.