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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Gunzel
New Member

Date Matrix into Tabular Data

Apologies if this has been asked before or is too basic, I'm unable to solve it. 

How am I able to convert the following table 

 

Names

01/02

01/02

02/02

02/02

 

Qty

Code

Qty

Code

X

10

E10

10

E10

Y

10

E10

10

E10

 

to 

 

Dates

Names

Qty

Code

01/02

X

10

E10

02/02

Y

10

E10

 

1 REPLY 1
ams1
Super User
Super User

Hi,

 

IF what you really want is:

ams1_0-1677592697657.png

 

And your input table looks like:

ams1_1-1677592738202.png

 

then one option is:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content], // CHANGE table name here
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Column1"}, {{"All", each _, type table [Column1=nullable text, Attribute=text, Value=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(#"Grouped Rows", each [Column1] = null)[All]{0}[Value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.SelectRows(#"Grouped Rows", each [Column1] = "Names")[All]{0}[Value]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns(Table.ToColumns([All]) & {[Custom]} & {[Custom.1]})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Column1] <> null) and ([Column1] <> "Names")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom.2"}),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.2", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom.2", List.Distinct(#"Expanded Custom.2"[Column3]), "Column3", "Column2"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Column4", "Dates"}, {"Column1", "Names"}})
in
    #"Renamed Columns"

 

 

Please mark this as answer if it helped.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors