Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have the following table in power query. I require to add rows based on the duration
Hence I need the final table to look like this.
Please help
Solved! Go to 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
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}}))
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.