Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Data Table Transformations Question

Hi All, 

 

I have a table structured like this:

  Currency typeEUR 
  Fiscal year2016 
  Version100 
  Period1 
CategoryCountry Country CapitalValue AValue 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  
  Country2016 
  Country Capital100 
  

Value A

Value B

1 
Currency typeFiscal yearVersionPeriod 

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

1 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"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

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?

Specializing in Power Query Formula Language (M)

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"
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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
Not applicable

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 typeEURnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
  Fiscal year2016nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
  Version100100nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull200nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
  Posting period11null23null4null5null6null7null8null9null10null11null12null1null2null3null4null5null6null7null8null9null10null11null12null
CategoryCountryCounrty CapitalValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue BValue AValue B
BikesEuropeBrussels-31918-133871143759666151559221572-4-133871143562-325502072910857-169771115502072910857-16977111961-4072910857-16977111961-40916-135-16977
nullUKLondon                -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
nullFranceParis38793-280675767148618-1357030705-47-280675466-45518246343705-5873219518246343705-5873219822326343705-58732198223280611-58732
nullnullnull951,298-731,390411,148-721,12391,198-561,417-80-731,39041665-27878261,218471,129-431,34180878261,218471,129-431,341801,331801,218471,129-431,341801,331801,317-49-431,341
FlowersEuropeBrussels-31918-133871143759666151559221572-4-133871143562-325502072910857-169771115502072910857-16977111961-4072910857-16977111961-40916-135-16977
nullUKLondon461,207-901,22012895966907-1568240796-55-901,220128636-206693794450893-31,0841156693794450893-31,0841151,1473094450893-31,0841151,147301,186-112-31,084
nullFranceParis34793680675767148618-1357030705-47680675466-47518246342705-5873217518246342705-5873217822306342705-58732178223080619-58732
nullnullnull38793-280675767148618-1357030705-47-280675466-45518246343705-5873219518246343705-5873219822326343705-58732198223280611-58732

 

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.