Adding Extra Virtual Rows to a Table

M

Magdoulin

New member
Joined
Aug 28, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
[FONT=&quot]Hi Guys,[/FONT]

[FONT=&quot]Hope you all are doing great.[/FONT]

[FONT=&quot]I think what I’m about to ask should be a bit complicated, but I count on your excellent knowledge of power query.[/FONT]

[FONT=&quot]I’ve that table at the bottom. What I wish to accomplish with the power query to add 10 extra rows, extending the column: Date, so the series would be from 8/1/2021 to 8/15/2021, but this occurs only in case column: Migrant value is: Yes, taking in account that:[/FONT]


  1. 1) The row to repeat is the latest entry of this particular name (meaning, that we will need to add 10 extra rows for the First Person only, not the Second Person, and the new rows will have dates from 8/6/2021 till 8/15/2021 and the age in these extra columns would be: 46, not 45, and the job is: Professor, not Teacher).
  2. 2) The newly added rows will need to have some of the columns empty, such as column: Country, column: Married and column: Having Children, so, the newly added rows should have the content of column: Name and column: Age and column: Job only.
[FONT=&quot]Is it some thing that could be done? I think yes, but how? I’m not quite sure.[/FONT]



[FONT=&quot]Current dataset:[/FONT]




[FONT=&quot]Desired output:[/FONT]

 
Your images are not showing - better to attach a sample workbook.

if this has been cross-posted elsewhere, you MUST provide links.
 
Current dataset:

1.png

Desired output:
1.png
 
Last edited:
And you probably expect that someone who wants to help you, either can create the correct code 100% from his head, or creates himself a folder with data to be able to test his code.

Instead of asking in several forums it would have been better to ask in only one forum, but to provide there also directly reasonable test data.
 
It's not the most elegant, but it gets the job done:
Code: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssKi5RCEgtKs7PU9JRMjEFEiGpickZqUVAVkB+TmJeCpARmVoMJP3y4UxDXcfSdKVYHfJNMKLYBGOKTTDBa0JAUX5aanFxPn4zTOFmBKcm5+elIAwxBhniV1pUnAqkfVMrMpPzYVrBBEowkqHZiBLNxpRoNqFEMzTAYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Job = _t, Country = _t, Married = _t, #"Having Children" = _t, Migrant = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Job", type text}, {"Country", type text}, {"Married", type text}, {"Having Children", type text}, {"Migrant", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"AllData", each _, type table [Name=nullable text, Age=nullable number, Job=nullable text, Country=nullable text, Married=nullable text, Having Children=nullable text, Migrant=nullable text, Date=nullable date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    ListDates = Table.AddColumn(#"Added Index", "ListDates", each if [Index] = 0  then 
{Number.From(List.Min([AllData][Date]))..Number.From(List.Min([AllData][Date])) + Table.RowCount([AllData]) + 9}
else
{Number.From(List.Min([AllData][Date]))..Number.From(List.Min([AllData][Date])) + Table.RowCount([AllData]) - 1}),
    SetNewDates = Table.AddColumn(ListDates, "SetDates", each Table.FromColumns(Table.ToColumns([AllData]) & {
[ListDates]})),
    RemoveOldDates = Table.AddColumn(SetNewDates, "RemoveOldDates", each Table.RemoveColumns([SetDates],{"Column8"})),
    RenameColumns = Table.AddColumn(RemoveOldDates, "RenameColumns", each Table.RenameColumns([RemoveOldDates],List.Zip({Table.ColumnNames([RemoveOldDates]),Table.ColumnNames([AllData])}))),
    SetDateType = Table.AddColumn(RenameColumns, "SetDateType", each Table.TransformColumnTypes([RenameColumns],{{"Date", type date}})),
    FillDown = Table.AddColumn(SetDateType, "FillDown", each Table.FillDown([SetDateType],{"Name","Age","Job","Migrant"})),
    #"Removed Other Columns" = Table.SelectColumns(FillDown,{"FillDown"}),
    Expanded = Table.ExpandTableColumn(#"Removed Other Columns", "FillDown", Table.ColumnNames(Source), Table.ColumnNames(Source)),
    RestoreTableTypes = Value.ReplaceType(Expanded,Value.Type(#"Changed Type"))
in
    RestoreTableTypes
 
Obviously, it’s my own call to post wherever I want as long I don’t break any forum rules.
The dataset’s just what’s displayed in the first screenshot here, very simple and basic table, nothing else to provide.
So, if you think it’s challenging question, pass it, others might be able to help, simple!
Thank you for your reply though, have a nice day.
 
It is certainly your choice to post where ever you wish, however, you will find that once people find that you have crossposted without notification, your help may disappear. This is contrary to what you were probably thinking when you thought to post to many sites. Here is a quick read to help you understand why this site and many others expect you to notify all that you have crossposted. Good Luck seeking a solution.

https://www.excelguru.ca/content.php?184
 
You must log in or register to reply here.
Back
Top