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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DaxBoi
New Member

Conditional Index M Language - Power Query

Hi guys I have a question I would appreciate help with


DATA 

 

I have a data table in the following format

 

Heading 101/01/2101/02/21
A12
B34
C56
Heading 201/01/2101/02/21
A12
C34
Heading 301/01/2101/02/21
D12


The aim is to have this data set look like 

 

Heading 1 01/01/2101/02/21
Heading 1A12
Heading 1B34
Heading 1C56
Heading 2 01/01/2101/02/21
Heading 2A12
Heading 2C34

 

Of course the following step is to remove the italic rows and unpivot the date columns.

 

QUESTION

 

I require some assistance in transforming the data table from the first example to the second.

 

I have added an index and conditional column with an if contains text "heading" but can't get much further than this.

 

My solution would be to add a column with a "1" for heading 1 and a "2" for heading 2 etc. and then using a lookup table later. 

Happy to take any advice

 

Thanks!

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @DaxBoi 

 

are you sure that you need some graphical output like your second table? Meaning that your headers are repeating throughout your data? Or should it look like this?

Jimmy801_0-1615283288721.png

 

if yes, here the approach to get this result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRMjDUByIjAyMYxwjCidWJVnIECoGEjcA8JyDLGIhNwDxnIMsUiM3APJiJRiSZ6IxiIswMYwJmuCDMiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"AllRows", each Table.TransformColumnNames(Table.PromoteHeaders(_), (x)=> if Text.StartsWith(x,"Heading") then "Value" else x)}}, GroupKind.Local, (x,y)=> if Text.StartsWith(y[Column1], "Headin") then 1 else 0),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Value", "01/01/2021", "01/02/2021"}, {"Value", "01/01/2021", "01/02/2021"})
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

View solution in original post

2 REPLIES 2
Jimmy801
Community Champion
Community Champion

Hello @DaxBoi 

 

are you sure that you need some graphical output like your second table? Meaning that your headers are repeating throughout your data? Or should it look like this?

Jimmy801_0-1615283288721.png

 

if yes, here the approach to get this result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRMjDUByIjAyMYxwjCidWJVnIECoGEjcA8JyDLGIhNwDxnIMsUiM3APJiJRiSZ6IxiIswMYwJmuCDMiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"AllRows", each Table.TransformColumnNames(Table.PromoteHeaders(_), (x)=> if Text.StartsWith(x,"Heading") then "Value" else x)}}, GroupKind.Local, (x,y)=> if Text.StartsWith(y[Column1], "Headin") then 1 else 0),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Value", "01/01/2021", "01/02/2021"}, {"Value", "01/01/2021", "01/02/2021"})
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @DaxBoi 

 

If you simply need Table 1 to Table 2, you can use Fill Down, paste the code in Advanced Editor. The Index column is to keep the original order

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRMjDUByIjAyMYxwjCidWJVnIECoGEjcA8JyDLGIhNwDxnIMsUiM3APJiJRiSZ6IxiIswMYwJmuCDMiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Column1],"Heading") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom1", each if Text.Contains([Column1],"Heading") then [Column1] else  [Custom]&[Column1])
in
    #"Added Custom1"

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors