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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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