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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ExcelMagic
New Member

Bringing row values to row above if column if null

I am importing a spreedsheet from a pdf so naturally the data is a little bit messy. Almost every other row has a null value in the first column "Queue" because their are some values in different columns that spill over into the next row. I would like to concatenate the spilled values when Queue is equal to null so the data would be transformed from

QueueValue1MW
1New 105
nullGeneratornull

to

QueueValues1MW
1New Generator105
1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

Hi @ExcelMagic ,
I would probably approach it like so:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Queue", Int64.Type}, {"Value1", type text}, {"MW", Int64.Type}}),
    CombineColumnNames = List.Skip(Table.ColumnNames(#"Changed Type")),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Queue"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Queue"}, {{"All", each #table(CombineColumnNames, {List.Transform(List.Skip(Table.ToColumns(_)), (l)=> Text.Combine(List.Transform(l, (i)=> Text.From(i)), " "))})}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", CombineColumnNames)
in
    #"Expanded All"

Please also check the file enclosed.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Community Champion
Community Champion

Hi @ExcelMagic ,
I would probably approach it like so:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Queue", Int64.Type}, {"Value1", type text}, {"MW", Int64.Type}}),
    CombineColumnNames = List.Skip(Table.ColumnNames(#"Changed Type")),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Queue"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Queue"}, {{"All", each #table(CombineColumnNames, {List.Transform(List.Skip(Table.ToColumns(_)), (l)=> Text.Combine(List.Transform(l, (i)=> Text.From(i)), " "))})}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", CombineColumnNames)
in
    #"Expanded All"

Please also check the file enclosed.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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