Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Level up your Power BI skills this month - build one visual each week and tell better stories with data! Get started

Reply
Anonymous
Not applicable

Generalizing a table with column reduction

‎01-18-2024 12:55 AM

Dear community,

 

actually i want to solve an easy task but i am struggling and hope that someone can help me.

 

I have the following table:

 

AlfredAlfred_1Alfred_2BertBert_1Bert_2ClaudiaClaudia_1Claudia_2
01.01.202301.06.20231001.06.202301.03.202410001.01.202301.07.20234
01.02.202301.06.20232001.07.202301.03.202411001.02.202301.07.20235
01.03.202301.06.20233001.08.202301.03.202412001.03.202301.07.20236
01.04.202301.06.20234001.09.202301.03.202413001.04.202301.07.20237
01.05.202301.06.20235001.10.202301.03.202414001.05.202301.07.20238
   01.11.202301.03.202415001.06.202301.07.20239
   01.12.202301.03.2024160   
   01.01.202401.03.2024170   
   01.02.202401.03.2024180   

 

and i want to transform the table to the following:

 

StartEndValueName
01.01.202301.06.202310Alfred
01.02.202301.06.202320Alfred
01.03.202301.06.202330Alfred
01.04.202301.06.202340Alfred
01.05.202301.06.202350Alfred
01.06.202301.03.2024100

Bert

01.07.202301.03.2024110Bert
01.08.202301.03.2024120Bert
01.09.202301.03.2024130Bert
........

 

I would be happy to receive all solutions

 

Greetings jb257

Labels:
Message 1 of 4
553 Views
1 ACCEPTED SOLUTION
slorin
Super User
Super User

‎01-18-2024 02:17 AM

Hi,

 

let
Source = Your_Source,
Name = List.Alternate(Table.ColumnNames(Source),2,1,1),
Data = List.Transform(List.Split(Table.ToColumns(Source),3), each Table.FromColumns(_, {"Start", "End", "Value"})),
#"Data+Name" = Table.FromColumns({Data}&{Name}, {"Data", "Name"}),
Expand = Table.ExpandTableColumn(#"Data+Name", "Data", {"Start", "End", "Value"}, {"Start", "End", "Value"}),
NotNull = Table.SelectRows(Expand, each [Start] <> null)
in
NotNull

 Stéphane

View solution in original post

Message 2 of 4
519 Views
3 REPLIES 3
dufoq3
Super User
Super User

‎01-18-2024 04:22 AM

You can also use this version. Just change 2nd step YourSource = Source to your data

dufoq3_0-1705580458931.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJdDoQgDATgqxiezab0B/AsxvtfYwVto+wkm0jigPP1QfY9Uf6cDxNLWkcoHjLNOz1IDzqO/fxVrx40HevFM+SZ5sabj/EMeQteIC9eb5iP8QL5ErxCXr2+YT7GK+Rr8AZ5u+uZMB/jDfJt8Mv55qtbGVsG/3NYG7QYW4Ue3y2oed0W/WnW/03GzTY1jy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Alfred = _t, Alfred_1 = _t, Alfred_2 = _t, Bert = _t, Bert_1 = _t, Bert_2 = _t, Claudia = _t, Claudia_1 = _t, Claudia_2 = _t]),
    YourSource = Source,
    #"Added Index" = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    Ad_Name = Table.AddColumn(#"Unpivoted Other Columns", "Name", each Text.BeforeDelimiter([Attribute], "_"), type text),
    #"Grouped Rows" = Table.Group(Ad_Name, {"Name"}, {{"All", each _, type table [Index=number, Attribute=text, Value=text, Name=text]}, {"Start", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name])[Value], type list}, {"End", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_1")[Value], type list}, {"Value", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_2")[Value], type list}}),
    Ad_MergedListsToTable = Table.AddColumn(#"Grouped Rows", "MergedListsToTable", each Table.SelectRows(Table.FromColumns({[Start], [End], [Value]}, {"Start", "End", "Value"}), (r)=> Text.Trim(r[Start]) <> null and Text.Trim(r[Start]) <> ""), type table),
    #"Removed Other Columns" = Table.SelectColumns(Ad_MergedListsToTable,{"MergedListsToTable", "Name"}),
    #"Expanded MergedTables" = Table.ExpandTableColumn(#"Removed Other Columns", "MergedListsToTable", {"Start", "End", "Value"}, {"Start", "End", "Value"})
in
    #"Expanded MergedTables"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 4 of 4
513 Views
slorin
Super User
Super User

‎01-18-2024 02:17 AM

Hi,

 

let
Source = Your_Source,
Name = List.Alternate(Table.ColumnNames(Source),2,1,1),
Data = List.Transform(List.Split(Table.ToColumns(Source),3), each Table.FromColumns(_, {"Start", "End", "Value"})),
#"Data+Name" = Table.FromColumns({Data}&{Name}, {"Data", "Name"}),
Expand = Table.ExpandTableColumn(#"Data+Name", "Data", {"Start", "End", "Value"}, {"Start", "End", "Value"}),
NotNull = Table.SelectRows(Expand, each [Start] <> null)
in
NotNull

 Stéphane

Message 2 of 4
520 Views
Anonymous
Not applicable
In response to slorin

‎01-18-2024 05:41 AM

Awesome, thank you very much!

Message 3 of 4
508 Views
0

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

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

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All
Top Solution Authors
View All