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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
gigotomo
Frequent Visitor

Need to add more rows in powerquery based on a column value.

Hello,

I have the following table in power query. I require to add rows based on the duration

 

gigotomo_1-1659450053155.png

Hence I need the final table to look like this.

gigotomo_2-1659450231097.png

Please help

1 ACCEPTED SOLUTION

Hi @gigotomo,

 

Something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqisUlDSUbLQNzDSNzIwMoKwjWFsEHbMyytNzFHwSU0sS1VQitWJVnJ0coZoMoQohHJM4RxjIHZOLEbT5urmDlZoZIqky8gMzsFiWSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Start of Leave" = _t, #"End of Leave" = _t, Duration = _t, #"Type of Leave" = _t]),
    #"Parsed Date" = Table.TransformColumns(Source,{{"Start of Leave", each Date.From(_, "us-en"), type date}, {"End of Leave", each Date.From(_, "us-en"), type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Parsed Date",{{"Employee Name", type text}, {"Start of Leave", type date}, {"End of Leave", type date}, {"Duration", Int64.Type}, {"Type of Leave", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Start of Leave])..Number.From([End of Leave])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Added to Column" = Table.TransformColumns(#"Expanded Range", {{"Range", Date.From, type date}})
in
    #"Added to Column"

 

Cheers,

John

View solution in original post

5 REPLIES 5
gigotomo
Frequent Visitor

@jbwtp 
Worked perfectly. Thanks for your help.

wdx223_Daniel
Super User
Super User

NewStep=#table({"Employee Name","Date","Shift"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Dates(x{1},x{3},Duration.From(1)),(x,y)=>{x{0},y,x{4}}))

jbwtp
Memorable Member
Memorable Member

Hi @gigotomo,

 

Do you want to try Unpivot Columns from the main or context menu?

 

It will do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqisUlDSUbLQNzDSNzIwMoKwjWFsEHbMyytNzFHwSU0sS1VQitWJVnJ0coZoMoQohHJM4RxjIHZOLEbT5urmDlZoZIqky8gMzsFiWSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Start of Leave" = _t, #"End of Leave" = _t, Duration = _t, #"Type of Leave" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Start of Leave", type text}, {"End of Leave", type text}, {"Duration", Int64.Type}, {"Type of Leave", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee Name", "Duration", "Type of Leave"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

Cheers,

John

Hello John,

 

Thanks for the solution but I already found a way using split column. What i did was

1. I created a dummy column with the value "a" repeated the same number of time as duration using Text.Repeat

2. Use "Split Column" but instead of splitting the column into columns, split the column into rows

3. delete the dummy column

 

gigotomo_0-1659499701788.png

But my problem now is how I increment the date value.

Hi @gigotomo,

 

Something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqisUlDSUbLQNzDSNzIwMoKwjWFsEHbMyytNzFHwSU0sS1VQitWJVnJ0coZoMoQohHJM4RxjIHZOLEbT5urmDlZoZIqky8gMzsFiWSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Start of Leave" = _t, #"End of Leave" = _t, Duration = _t, #"Type of Leave" = _t]),
    #"Parsed Date" = Table.TransformColumns(Source,{{"Start of Leave", each Date.From(_, "us-en"), type date}, {"End of Leave", each Date.From(_, "us-en"), type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Parsed Date",{{"Employee Name", type text}, {"Start of Leave", type date}, {"End of Leave", type date}, {"Duration", Int64.Type}, {"Type of Leave", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Start of Leave])..Number.From([End of Leave])}),
    #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Added to Column" = Table.TransformColumns(#"Expanded Range", {{"Range", Date.From, type date}})
in
    #"Added to Column"

 

Cheers,

John

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Kudoed Authors