Why not, Shubham? Recording your techniques and sharing them has many benefits, including building your library to where you can refer to when you need to. 😉
Maybe you can try to merge the columns first using the button for that available in Power Query ribbon, and then you can merge the rows using this technique if this works for you.
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
Hi, Faisal. Maybe you can try the function Time.StartOfHour learn.microsoft.com/en-us/powerquery-m/time-startofhour?WT.mc_id=M365-MVP-5003849 Imagining your have a column "Time", you can create a conditional column with something like: If Time.StarOfHour([Time]) < #time(8, 0, 0) then #time(8,0,0) else [Time] I did not test this, but I hope it helps.
@@CeliaAlvesSolveExcel Thanks for answering. But I do not want to merge the entire columns, Suppose I just want to merge row 1 of two different columns, how to do that?
You may need to use M Code. Watch this video where at some point I explain how to get the value from one cell with Power Query. Do this twice for each cell and then create a new step to concatenate the results with "&" between the two step names. ruclips.net/video/4fs9BOHKTSA/видео.html
This is very good BUT I'm importing data from folder and the folder contains 200+ .pdf files where the Name field 1st row starts with # IF the Title is long it wraps onto a second row BUT not always so how to I join Title names that have flowed onto the second row BUT leave shorter Title names as is?
Thank you very much Celia! Really feel falttered for being mentioned. Very thorough guide, will try to implement and come back with further questions. I noticed the wrapped around line of text in excel file when imported into power query generates a hidden #(LF) the line feed code; and was wondering if I could simply create a conditional column or use find/replace that removes the #(LF) before Power Query auto generates two lines for the same label with wrapped around line. Wonder about the wrapped around line that spills over to a third line. Your solution can be scaled for varying lengths of groups, I imagine.
You're welcome, Iftikhar. Your suggestions to solve the line feed character should work. Another option, if applicable is to remove wrap formatting from the source file. As for varying group lengths in the same data set, this solution does not work, unless you have a column that clearly identifies each group. If you do, then you can apply group by to that column.
Hi Celia. Great challenge and solution. It can also be done in one query by adding a Custom Column after the Group By step with the following (my grouped tables are in a column labeled as AllRows): = Text.Combine([AllRows][Description], " "). That extracts and combines the text from the Description column in the nested tables into a new column and then you can expand the nested tables column, remove other columns, change type, etc. No need to Merge Queries with this method. I posted the full M code below. Power Query offers so many options to solve problem.. one of the things that makes it such a great and valuable tool. As always, thanks for the tips! Thumbs up!! let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"AllRows", each _, type table [Description=text, Date=datetime, Min=number, Max=number, Index=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Description", each Text.Combine([AllRows][Description], " ")), #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Custom", "AllRows", {"Description", "Timestamp", "Min", "Max", "Index"}, {"Description", "Timestamp", "Min", "Max", "Index.1"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded AllRows",{"New Description", "Timestamp", "Min", "Max"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"New Description", type text}, {"Timestamp", type datetime}, {"Min", type number}, {"Max", type number}}) in #"Changed Type"
Thank you so much for this tip. I searched ages for such a solution for a PowerBI problem. Excellent!
Awesome! Glad that I could help @franker69!
Great Example and very clever solution Celia.. using the index and division by 2 to create the proper sets . Thanks for sharing..
Thank you! Glad that you enjoyed it. :)
Awesome processing. Thank you for sharing
Thanks for sharing ❤
You're welcome!
Thanks Celia :) :) That was very helpful!!!
You're welcome, John! Glad that I could help.
This is awesome and very useful!!! Thank you Celia! 😊
You're welcome, Alejandra. Glad that you enjoyed it.
Great video!
Thank you, Kenneth.:)
PowerQ awesome as always...I use these type of techniques daily in office...should I start my own youtube channel...??
Why not, Shubham? Recording your techniques and sharing them has many benefits, including building your library to where you can refer to when you need to. 😉
Deus continue te abençoando ❤️❤️❤️
Amen! :)
I need to merge a cell vertical and merge three or four cells across from it to that first cell
Maybe you can try to merge the columns first using the button for that available in Power Query ribbon, and then you can merge the rows using this technique if this works for you.
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
Hi, Faisal. Maybe you can try the function Time.StartOfHour
learn.microsoft.com/en-us/powerquery-m/time-startofhour?WT.mc_id=M365-MVP-5003849
Imagining your have a column "Time", you can create a conditional column with something like:
If Time.StarOfHour([Time]) < #time(8, 0, 0) then #time(8,0,0) else [Time]
I did not test this, but I hope it helps.
Please tell me how to merge two cells in the same row in power query
One option is select the two columns, go to transform > merge columns
@@CeliaAlvesSolveExcel Thanks for answering. But I do not want to merge the entire columns, Suppose I just want to merge row 1 of two different columns, how to do that?
You may need to use M Code. Watch this video where at some point I explain how to get the value from one cell with Power Query. Do this twice for each cell and then create a new step to concatenate the results with "&" between the two step names.
ruclips.net/video/4fs9BOHKTSA/видео.html
Could split string in cell to split case and line then just use case number.
Hi, Ned can you explain your question with an example of what you want to happen?
This is very good BUT I'm importing data from folder and the folder contains 200+ .pdf files where the Name field 1st row starts with # IF the Title is long it wraps onto a second row BUT not always so how to I join Title names that have flowed onto the second row BUT leave shorter Title names as is?
to help this the following cell is always "Status" so it must me join cells between a cell starting with # before "Status"
so how to merge cell vertically that are between a cell that start with # and a cell that is Status?
Thank you very much Celia! Really feel falttered for being mentioned. Very thorough guide, will try to implement and come back with further questions. I noticed the wrapped around line of text in excel file when imported into power query generates a hidden #(LF) the line feed code; and was wondering if I could simply create a conditional column or use find/replace that removes the #(LF) before Power Query auto generates two lines for the same label with wrapped around line. Wonder about the wrapped around line that spills over to a third line. Your solution can be scaled for varying lengths of groups, I imagine.
You're welcome, Iftikhar.
Your suggestions to solve the line feed character should work. Another option, if applicable is to remove wrap formatting from the source file.
As for varying group lengths in the same data set, this solution does not work, unless you have a column that clearly identifies each group. If you do, then you can apply group by to that column.
Hi Celia. Great challenge and solution. It can also be done in one query by adding a Custom Column after the Group By step with the following (my grouped tables are in a column labeled as AllRows): = Text.Combine([AllRows][Description], " "). That extracts and combines the text from the Description column in the nested tables into a new column and then you can expand the nested tables column, remove other columns, change type, etc. No need to Merge Queries with this method. I posted the full M code below. Power Query offers so many options to solve problem.. one of the things that makes it such a great and valuable tool. As always, thanks for the tips! Thumbs up!!
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"AllRows", each _, type table [Description=text, Date=datetime, Min=number, Max=number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Description", each Text.Combine([AllRows][Description], " ")),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Custom", "AllRows", {"Description", "Timestamp", "Min", "Max", "Index"}, {"Description", "Timestamp", "Min", "Max", "Index.1"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded AllRows",{"New Description", "Timestamp", "Min", "Max"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"New Description", type text}, {"Timestamp", type datetime}, {"Min", type number}, {"Max", type number}})
in
#"Changed Type"
Thank you very much for sharing this, Wayne. This is awesome! Your proposal must be more efficient than using Merge.
@@CeliaAlvesSolveExcel Thanks Celia! Glad to contribute :))