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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bobhy
Helper I
Helper I

Inserting previous rows

I am expecting output similar to the one outlined in the chart below under the heading "Expected output". Please see the chart below for more info. Thanks in advance for your help on this.

My requirements are:

1) Take number of rows from where combination of the respective ID & Code is maximum for Assignment & AssignmentDesc is not null for column Mode='BER' and insert those rows for column Mode='AER'.

2) Newly inserted rows for column Mode="AER" should carry Assignment,AssignmentDesc, Percentage column values from their copied/previous rows. Here Percentage is a "New column"(Created in the report, but not derived from the database).

3) Newly inserted rows for column Mode="AER" should have the existing values as it is for ID, Code, Mode and Cost columns.

4) My source is Database and data consists of around 50K rows.

 

Current Data      
IDCodeAssignmentAssignmentDescCostModePercentage
20010  250BER10%
2001034JDC120.19BER15%
2001018OB 58.88BER25%
2001051FFY82.42BER13%
20020  320BER45%
2002032AOL45.34BER67%
20060  500AER40%
20030  1084.51AER35%
30010  500BER80%
3001034JDC235.67BER10%
3001023LPT456.34BER45%
30040  700BER35%
3004051FFY340BER45%
3004045KT290BER80%
3004018OB 2345.78BER20%
30090  1000AER10%
       
       
Expected Output      
IDCodeAssignmentAssignmentDescCostModePercentage
20010  250BER10%
2001034JDC120.19BER15%
2001018OB 58.88BER25%
2001051FFY82.42BER13%
20020  320BER45%
2002032AOL45.34BER67%
20060  500AER40%
2006034JDC500AER15%
2006018OB 500AER25%
2006051FFY500AER13%
20030  1084.51AER35%
2003034JDC1084.51AER15%
2003018OB 1084.51AER25%
2003051FFY1084.51AER13%
30010  500BER80%
3001034JDC235.67BER10%
3001023LPT456.34BER45%
30040  700BER35%
3004051FFY340BER45%
3004045KT290BER80%
3004018OB 2345.78BER20%
30090  1000AER10%
3009051FFY1000AER45%
3009045KT1000AER80%
3009018OB 1000AER20%

 

 

24 REPLIES 24
bobhy
Helper I
Helper I

Please see my clarifications in bold below.

 

In the expected output, row(s) are required to be added after

20060  500AER40%

Yes

 

What's the logic of the contents to be added? (i.e. see below)

Based on Cost and Percentage, we will be creating a new column to show Cost*Percentage (For example : 500*15%)

As per expected output, 3 rows added based on code 10.

2006034JDC500AER15%
2006018OB 500AER25%
2006051FFY500AER13%

 

What about code 20?

2002032AOL45.34BER67%

Here, we take the number of rows from where the combination of the respective ID & Code is maximum in addition to the  Assignment & AssignmentDesc being not null for column Mode='BER' and insert those rows for column Mode='AER'. In the chart aforementioned,  ID:200 and code:10 have maximum number of rows (3) in comparision to ID:200 and code:20.

 

Thanks,

Bobhy

 

Hi @bobhy ,

 

My first attempt applied the insert rows method and returned an error. So, I switched to the append method for now.

KT_Bsmart2gethe_0-1656661714765.png

 

The logic:

1. Get the table that we need to insert (Applied grouped to get the max)

2. Append step 1 to original table, and then sort by ID, Code.

 

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHNCsIwEIRfRQLeSkj2J0mPrdqDFiriRYrv/xrmp9iNwUMH0nzMzk7WVYExqlM2yWH7gNNpvDzyxVG9uwpDinI9n9IZjLb9DnMD2xBlGZMtBx3Cl4WWZRtlml5RA2iC3RclCzIrwp6VuMEwmQzLnG91Tl5Y5yXrpCXnX0OxrNZHiVkTSOfIBcVtOraFFscyOJgGE4UCsna+aV/AgFHm+zNv5ORKVAUgGcCLANhioniktk9BEke5peHQ/9uJft4dMFbvxctXdF93KrrPy78/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Assignment = _t, AssignmentDesc = _t, Cost = _t, Mode = _t, Percentage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}, {"Percentage", Percentage.Type}}),
GetMax = Table.Join(Table.Group(Table.Group(#"Changed Type", {"ID", "Code"}, {{"Count", each Table.RowCount(_), Int64.Type}}), {"ID"}, {{"Max", each List.Max([Count]), type number}}),{"ID", "Max"}, Table.Group(#"Changed Type", {"ID", "Code"}, {{"Count", each Table.RowCount(_), Int64.Type}}), {"ID", "Count"}),
InsertTable = Table.RemoveColumns(Table.Join(Table.PrefixColumns(Table.SelectColumns(Table.SelectRows(#"Changed Type", each [Mode]="AER"),{"ID", "Code", "Mode"}),"Temp"),{"Temp.ID"},Table.SelectColumns(Table.Join(Table.SelectRows(Table.Join(#"Changed Type",{"ID", "Code"},GetMax,{"ID", "Code"},JoinKind.Inner), each [AssignmentDesc]<>" "),{"ID"},Table.FromList(List.Distinct(Table.SelectRows(#"Changed Type", each ([Mode] = "AER"))[ID]),Splitter.SplitByNothing()), {"Column1"}, JoinKind.Inner), List.Select(Table.ColumnNames(#"Changed Type"), each _<>"Mode" and _<>"Code" )),{"ID"}), {"ID"}),
Combine = Table.Combine({#"Changed Type", Table.RenameColumns(InsertTable,Table.ToRows(Table.AddColumn(Table.FromList(List.Select(Table.ColumnNames(InsertTable), each Text.StartsWith(_,"Temp")),Splitter.SplitByNothing()),"Column2",each Text.AfterDelimiter([Column1],"Temp."))))}),
#"Sorted Rows" = Table.Sort(Combine,{{"ID", Order.Ascending}, {"Code", Order.Ascending}})
in
#"Sorted Rows"

 

 

Let me know if it works for you. I will have another go on the insert method.

 

Regards

KT

Receiving the error "Invalid Column Name" - Percentage . Here column "Percentage" is a calculated column that is created in Report Model, it is not derived from the database.

Our source table has nearly 50k rows and will be eventually growing. 

Thanks,

Bobhy

Hi @BobH,

 

Add the GetMax step after the step added the percentage column.

 

I do understand that your data is 50k and growing. I avoid using Table.NestedJoin, which will slow up the query loading time. Instead, I applied Table.Join and hope it smooths out the performance.

 

PreviousStep ......

GetMax = Table.Join(Table.Group(Table.Group(PreviousStep, {"ID", "Code"}, {{"Count", each Table.RowCount(_), Int64.Type}}), {"ID"}, {{"Max", each List.Max([Count]), type number}}),{"ID", "Max"}, Table.Group(PreviousStep, {"ID", "Code"}, {{"Count", each Table.RowCount(_), Int64.Type}}), {"ID", "Count"}),
InsertTable = Table.RemoveColumns(Table.Join(Table.PrefixColumns(Table.SelectColumns(Table.SelectRows(PreviousStep, each [Mode]="AER"),{"ID", "Code", "Mode"}),"Temp"),{"Temp.ID"},Table.SelectColumns(Table.Join(Table.SelectRows(Table.Join(PreviousStep,{"ID", "Code"},GetMax,{"ID", "Code"},JoinKind.Inner), each [AssignmentDesc]<>" "),{"ID"},Table.FromList(List.Distinct(Table.SelectRows(PreviousStep, each ([Mode] = "AER"))[ID]),Splitter.SplitByNothing()), {"Column1"}, JoinKind.Inner), List.Select(Table.ColumnNames(PreviousStep), each _<>"Mode" and _<>"Code" )),{"ID"}), {"ID"}),
Combine = Table.Combine({PreviousStep, Table.RenameColumns(InsertTable,Table.ToRows(Table.AddColumn(Table.FromList(List.Select(Table.ColumnNames(InsertTable), each Text.StartsWith(_,"Temp")),Splitter.SplitByNothing()),"Column2",each Text.AfterDelimiter([Column1],"Temp."))))}),
#"Sorted Rows" = Table.Sort(Combine,{{"ID", Order.Ascending}, {"Code", Order.Ascending}})
in
#"Sorted Rows"

 

Regards

KT 

I am not sure what steps I need to include in the place of "PreviousStep" in the code? Can you please provide the entire code.

Also, I am getting the error "Invalid Column Name" - Percentage ( Calculated Column) at Source step.

 

Hi @bobhy ,

 

Please see below solutions file.

Solution 

 

There are 2 solutions:

1. Append method;

2. Insert method.

 

You can try both methods to see which one provides you with better performance.

 

To integrate the code with your existing one.

 

I suggest you delete all the steps after you added the "Percentage" Calculation column, and you rename the last step to PreviousStep, then add the code from the GetMax step in the advance editor. PQ with aligning all the names for you; therefore, you're not required to update the remainder codes.

KT_Bsmart2gethe_0-1656694103549.png

 

 

Regards

KT

 

I tried the steps that you suggested but couldn't get the expected output as per the aforementioned chart.

My other option is inserting/duplicating fixed number of rows (3 rows)  for mode=AER . One ID can have multiple AERs. I am expecting output similar to the one outlined in the chart below under the heading "Expected output". Please see the chart below for more info. 

 

Current Data     
IDCodeAssignmentAssignmentDescCostMode
20010  250BER
2001034JDC120.19BER
2001018OB 58.88BER
2001051FFY82.42BER
20020  320BER
2002032AOL45.34BER
20060  500AER
20030  1084.51AER
30010  500BER
3001034JDC235.67BER
3001023LPT456.34BER
30040  700BER
3004051FFY340BER
3004045KT290BER
3004018OB 2345.78BER
30090  1000AER
      
      
Expected Output    
IDCodeAssignmentAssignmentDescCostMode
20010  250BER
2001034JDC120.19BER
2001018OB 58.88BER
2001051FFY82.42BER
20020  320BER
2002032AOL45.34BER
20060  500AER
20060  500AER
20060  500AER
20060  500AER
20030  1084.51AER
20030  1084.51AER
20030  1084.51AER
20030  1084.51AER
30010  500BER
3001034JDC235.67BER
3001023LPT456.34BER
30040  700BER
3004051FFY340BER
3004045KT290BER
3004018OB 2345.78BER
30090  1000AER
30090  1000AER
30090  1000AER
30090  1000AER

 

Thanks,

Bobhy

Hi @bobhy ,

 

Hopefully, I will be able to assist you in resolving your challenge very soon.

To achieve a better outcome, I have some questions:

 

1. Have you had a look at the attached pbix file?

KT_Bsmart2gethe_1-1656746834751.png

 

If yes, does the result in your desired outcome?

 

2. Are you expecting any content to fill in the highlighted areas as the expected output differs from the initial post.

KT_Bsmart2gethe_0-1656746787346.png

 

3. Would you mind sharing your code, so I can take a look and help you join the codes?

It is challenging to diagnose where are steps/codes required to update to have the code work as per your desired outcome.

Please see my answers in bold below. As of now, I need your help on my recent post that is
" inserting/duplicating fixed number of rows (3 rows) for mode=AER". Thanks for your help.

 

2. Are you expecting any content to fill in the highlighted areas as the expected output differs from the initial post.

bobhy_0-1656775556336.png

No

 

3. Would you mind sharing your code, so I can take a look and help you join the codes?

Sorry, I will not be able to share my code as it contains confidential data.

Hi @bobhy ,

 

As per your latest post, 3 fixed rows were duplicated after each "AER"'s rows.

 

Please see below screenshot:

KT_Bsmart2gethe_0-1656806277072.png

 

Source is based on the latest post (i.e. without the "percentage" column).

 

Please open a blank query, delete all the default code and paste the code below. You should be able to see how it works.

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHLDoIwEEV/xXRNmnYeMCxFZaEkGOPGEP7/N5wWja0tC07S5jBzc7ssBpwzjfEBh88HHE7D5WHWJhOQFNfzKZzBWd/XNS+KeQijWKxI3WKvGMeXUsASFBakmRDKTPEKw4/HeVIS25gwt9p0DMerYyZgKngnZGO0r4RlQduUoRSSggDZtl1dA1RM92fM3OahN43SdV1lHf1ViLTjECtuYRX0O0ryXoBaYieF1+cd/Vpc3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Assignment = _t, AssignmentDesc = _t, Cost = _t, Mode = _t]),
PreviousStep = Table.TransformColumnTypes(
Source,
{
{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}
}
),

//Get Position of AER
AER_Index = List.Sort(
List.PositionOf(
PreviousStep[Mode],
"AER",
Occurrence.All
),
Order.Descending
),
//Insert Records after "AER"'s row(s)
InsertRecords = List.Accumulate(
{0..List.Count(AER_Index)-1},
PreviousStep,
(c,d) => Table.InsertRows(
c,
AER_Index{d}+1,
Table.ToRecords(
Table.Combine(
{
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep))
}
)
)
)
)
in
InsertRecords

 

Regards

KT

Newly inserted rows are showing up in the Power Query Editor but when I click on Close&Apply the rows are not showing up in the report view. Can you please look into this.

Thanks,

Bobhy

Hi @bobhy ,

 

That's great to hear that you are able to see the duplicated rows. I have tested from my end, and the query loaded with no issue. Please see below screenshot. 

KT_Bsmart2gethe_0-1656820815452.png

The information you described above is insufficient for me to provide further advice (i.e. Click and Load).

However, there are a few things you may want to look at, such as if there are any other errors in other queries preventing you from loading it or if there are any errors message.

 

Regards

KT (Volunteer)

Here is the code and the screen shots. Can you please take a look and let me know why it is not showing up in report view when I click on "Click&Apply" in Power Query editor. My source is database.

 

let
    Source = Sql.Database("LAPTOP-0A8351HV\SQLEXPRESS", "master", [Query="select ID,Code,Assignment,AssignmentDesc,Cost,Mode from InsertRows_Table_June22 "]),
    dbo_InsertRows_TABLE_June22 = Source{[Schema="dbo",Item="InsertRows_TABLE_June22"]}[Data],

PreviousStep = Table.TransformColumnTypes(
Source,
{
{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}
}
),

//Get Position of AER
AER_Index = List.Sort(
List.PositionOf(
PreviousStep[Mode],
"AER",
Occurrence.All
),
Order.Descending
),
//Insert Records after "AER"'s row(s)
InsertRecords = List.Accumulate(
{0..List.Count(AER_Index)-1},
PreviousStep,
(c,d) => Table.InsertRows(
c,
AER_Index{d}+1,
Table.ToRecords(
Table.Combine(
{
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),
Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep))
}
)
)
)
)
in
InsertRecords

 

Power Query Editor Screenshot

bobhy_0-1656822669410.png

 

Report View Screenshot

bobhy_1-1656822724456.png

 

Thanks

Bobhy

Hi @bobhy ,

 

You selected Table as the visual with "Don't Summarise". 

The way the visual constructed will only show you unique items (i.e. if 4 rows are identical and the table will only present one).

If you need those added rows presented in the format as per screenshot's table, the duplicated rows are required to be unique.

The first solutions will work better in this case.

KT_Bsmart2gethe_0-1656824571722.png

 

I updated the code from the first solution. Feel free to test it.

let
Source = Sql.Database("LAPTOP-0A8351HV\SQLEXPRESS", "master", [Query="select ID,Code,Assignment,AssignmentDesc,Cost,Mode from InsertRows_Table_June22 "]),
dbo_InsertRows_TABLE_June22 = Source{[Schema="dbo",Item="InsertRows_TABLE_June22"]}[Data],
PreviousStep = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),

//Get the MaxCount of Code
GetMax = Table.PrefixColumns(
Table.Join(
Table.Group(
Table.Group(
PreviousStep,
{"ID", "Code"},
{
{"Count", each Table.RowCount(_), Int64.Type}
}
),
{"ID"},
{
{"Max", each List.Max([Count]), type number}
}
),
{"ID", "Max"},
Table.Group(
PreviousStep,
{"ID", "Code"},
{
{"Count", each Table.RowCount(_), Int64.Type}
}
),
{"ID", "Count"}
),
"m"
),
//Get Records
Record = Table.AddColumn(
Table.ExpandListColumn(
Table.AddColumn(
Table.AddColumn(
Table.SelectRows(
Table.SelectColumns(
Table.Join(
PreviousStep, {"ID", "Code"}, GetMax, {"m.ID", "m.Code"}, JoinKind.Inner
),
List.Select(
Table.ColumnNames(PreviousStep),
each not Text.StartsWith(_, "m.") and (_ <> "Code") and (_ <> "Mode")
)
),
each [AssignmentDesc] <> " "
),
"Mode", each "AER"
),
"Code", (x) => Table.SelectRows(PreviousStep, each x[ID]=[ID] and x[Mode]=[Mode])[Code]
),
"Code"
),
"Record", each _
),
//Get Position of AER
AER_Index = List.Sort(
List.PositionOf(
PreviousStep[Mode],
"AER",
Occurrence.All
),
Order.Descending),
//Insert Records after "AER"'s row(s)
InsertRecords = List.Accumulate(
{0..List.Count(AER_Index)-1},
PreviousStep,
(c,d) => Table.InsertRows(
c,
AER_Index{d}+1,
Table.SelectRows(Record, each [Code]=c[Code]{AER_Index{d}})[Record]
)
)
in
InsertRecords

 

Regards

KT

 

Its not working. I want to go with my option of inserting/duplicating fixed number of rows (3 rows)  for mode=AER (One ID can have multiple AERs). I understand your point of the duplicated rows required to be unique. Is there any work around to overcome this ? 

Hi @bobhy ,

 

Please see below screenshot shows the workaround result and the query steps:

KT_Bsmart2gethe_0-1656884281475.png

I applied replace value function to "InsertRecords" step and add one more step to keep the visual consistent.

KT_Bsmart2gethe_1-1656884411089.png

 

Below is the updated code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHLDoIwEEV/xXRNmnYeMCxFZaEkGOPGEP7/N5wWja0tC07S5jBzc7ssBpwzjfEBh88HHE7D5WHWJhOQFNfzKZzBWd/XNS+KeQijWKxI3WKvGMeXUsASFBakmRDKTPEKw4/HeVIS25gwt9p0DMerYyZgKngnZGO0r4RlQduUoRSSggDZtl1dA1RM92fM3OahN43SdV1lHf1ViLTjECtuYRX0O0ryXoBaYieF1+cd/Vpc3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Assignment = _t, AssignmentDesc = _t, Cost = _t, Mode = _t]),
PreviousStep = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", type number}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),

//Get Position of AER
AER_Index = List.Sort(
List.PositionOf(
PreviousStep[Mode],
"AER",
Occurrence.All
),
Order.Descending
),
//Insert Records after "AER"'s row(s)
InsertRecords = List.Accumulate(
{0..List.Count(AER_Index)-1},
PreviousStep,
(c,d) => Table.InsertRows(
c,
AER_Index{d}+1,
Table.ToRecords(
Table.Combine(
{
Table.ReplaceValue(Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),null,0.001,Replacer.ReplaceValue,{"Assignment"}),
Table.ReplaceValue(Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),null,0.002,Replacer.ReplaceValue,{"Assignment"}),
Table.ReplaceValue(Table.FromRecords({PreviousStep{AER_Index{d}}},Table.ColumnNames(PreviousStep)),null,0.003,Replacer.ReplaceValue,{"Assignment"})
}
)
)
)
),
#"Replaced Value" = Table.ReplaceValue(InsertRecords,null,0,Replacer.ReplaceValue,{"Assignment"})
in
#"Replaced Value"

 

Regards

KT

Inserting/duplicating fixed number of rows (3 rows) for mode=AER is working good. Thanks a lot for your help.

I am just wondering whether it is possible to copy/fill the "Assignment","AssignmentDesc" and "Percentage" columns values from their respective ID previous records ? This I need in the same logic where it is inserting fixed number of rows. I am expecting output similar to the one outlined in the chart below under the heading "Expected output". Please see the chart below for more info. Can you please help me on this.

Current Output      
IDCodeAssignmentAssignmentDescCostModePercentage
20010  250BER10%
2001034JDC120.19BER15%
2001018OB 58.88BER25%
2001051FFY82.42BER13%
20060  500AER40%
20030  1084.51AER35%
30040  700BER35%
3004051FFY340BER45%
3004045KT290BER80%
3004018OB 2345.78BER20%
30090  1000AER10%
       
       
Expected Output      
IDCodeAssignmentAssignmentDescCostModePercentage
20010  250BER10%
2001034JDC120.19BER15%
2001018OB 58.88BER25%
2001051FFY82.42BER13%
20060  500AER40%
2006034JDC500AER15%
2006018OB 500AER25%
2006051FFY500AER13%
20030  1084.51AER35%
2003034JDC1084.51AER15%
2003018OB 1084.51AER25%
2003051FFY1084.51AER13%
30040  700BER35%
3004051FFY340BER45%
3004045KT290BER80%
3004018OB 2345.78BER20%
30090  1000AER10%
3009051FFY1000AER45%
3009045KT1000AER80%
3009018OB 1000AER20%

 

HI @bobhy ,

 

I know you mentioned in the original post that the percentage calculation does not come from the db. I was wondering what is the formula to calculate the %?

I can add it in the code followed by the SQL code so that you won't get the error.

 

Regards

KT

Now, I have exported the report output into an excel file. The source will be an excel file.
So, there will be no formula in the "Percentage" column as it is coming from the excel file.

Thanks,

Bobhy

Hi @bobhy ,

 

Please see code below:

 

Source = Your SQL code including % column,
PreviousStep = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}, {"Percentage", Percentage.Type}}),
//Get the MaxCount of Code
GetMax = Table.PrefixColumns(
Table.Join(
//
Table.Group(
//Group to get Max Count
Table.Group(
PreviousStep,
{"ID", "Code"},
{
{"Count", each Table.RowCount(_), Int64.Type}//,
//{"Cost", each List.Sum([Cost]), type nullable number}
}
),
{"ID"},
{
{"Max", each List.Max([Count]), type number}
}
),
{"ID", "Max"},
Table.Group(
PreviousStep,
{"ID", "Code"},
{
{"Count", each Table.RowCount(_), Int64.Type}
}
),
{"ID", "Count"}
),
"m"
),
//Get Records
Record = Table.AddColumn(
Table.AddColumn(
Table.ExpandListColumn(
Table.AddColumn(
Table.AddColumn(
Table.SelectRows(
Table.SelectColumns(
Table.Join(
PreviousStep, {"ID", "Code"}, GetMax, {"m.ID", "m.Code"}, JoinKind.Inner
),
List.Select(
Table.ColumnNames(PreviousStep),
each not Text.StartsWith(_, "m.") and (_ <> "Code") and (_ <> "Mode") and (_ <> "Cost")
)
),
each [AssignmentDesc] <> " "
),
"Mode", each "AER"
),
"Code",
(x) => Table.SelectRows(PreviousStep, each x[ID]=[ID] and x[Mode]=[Mode])[Code]
),
"Code"
),
"Cost",
(x) => Table.SelectRows(PreviousStep, each x[ID]=[ID] and x[Mode]=[Mode] and x[Code]=[Code])[Cost]{0}
),
"Record", each _
),
//Get Position of AER
AER_Index = List.Sort(
List.PositionOf(
PreviousStep[Mode],
"AER",
Occurrence.All
),
Order.Descending),
//Insert Records after "AER"'s row(s)
InsertRecords = List.Accumulate(
{0..List.Count(AER_Index)-1},
PreviousStep,
(c,d) => Table.InsertRows(
c,
AER_Index{d}+1,
Table.SelectRows(Record, each [Code]=c[Code]{AER_Index{d}})[Record]
)
)
in
InsertRecords

 

Regards

KT

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors