Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | Percentage |
200 | 10 | 250 | BER | 10% | ||
200 | 10 | 34 | JDC | 120.19 | BER | 15% |
200 | 10 | 18 | OB | 58.88 | BER | 25% |
200 | 10 | 51 | FFY | 82.42 | BER | 13% |
200 | 20 | 320 | BER | 45% | ||
200 | 20 | 32 | AOL | 45.34 | BER | 67% |
200 | 60 | 500 | AER | 40% | ||
200 | 30 | 1084.51 | AER | 35% | ||
300 | 10 | 500 | BER | 80% | ||
300 | 10 | 34 | JDC | 235.67 | BER | 10% |
300 | 10 | 23 | LPT | 456.34 | BER | 45% |
300 | 40 | 700 | BER | 35% | ||
300 | 40 | 51 | FFY | 340 | BER | 45% |
300 | 40 | 45 | KT | 290 | BER | 80% |
300 | 40 | 18 | OB | 2345.78 | BER | 20% |
300 | 90 | 1000 | AER | 10% | ||
Expected Output | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | Percentage |
200 | 10 | 250 | BER | 10% | ||
200 | 10 | 34 | JDC | 120.19 | BER | 15% |
200 | 10 | 18 | OB | 58.88 | BER | 25% |
200 | 10 | 51 | FFY | 82.42 | BER | 13% |
200 | 20 | 320 | BER | 45% | ||
200 | 20 | 32 | AOL | 45.34 | BER | 67% |
200 | 60 | 500 | AER | 40% | ||
200 | 60 | 34 | JDC | 500 | AER | 15% |
200 | 60 | 18 | OB | 500 | AER | 25% |
200 | 60 | 51 | FFY | 500 | AER | 13% |
200 | 30 | 1084.51 | AER | 35% | ||
200 | 30 | 34 | JDC | 1084.51 | AER | 15% |
200 | 30 | 18 | OB | 1084.51 | AER | 25% |
200 | 30 | 51 | FFY | 1084.51 | AER | 13% |
300 | 10 | 500 | BER | 80% | ||
300 | 10 | 34 | JDC | 235.67 | BER | 10% |
300 | 10 | 23 | LPT | 456.34 | BER | 45% |
300 | 40 | 700 | BER | 35% | ||
300 | 40 | 51 | FFY | 340 | BER | 45% |
300 | 40 | 45 | KT | 290 | BER | 80% |
300 | 40 | 18 | OB | 2345.78 | BER | 20% |
300 | 90 | 1000 | AER | 10% | ||
300 | 90 | 51 | FFY | 1000 | AER | 45% |
300 | 90 | 45 | KT | 1000 | AER | 80% |
300 | 90 | 18 | OB | 1000 | AER | 20% |
Please see my clarifications in bold below.
In the expected output, row(s) are required to be added after
200 | 60 | 500 | AER | 40% |
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.
200 | 60 | 34 | JDC | 500 | AER | 15% |
200 | 60 | 18 | OB | 500 | AER | 25% |
200 | 60 | 51 | FFY | 500 | AER | 13% |
What about code 20?
200 | 20 | 32 | AOL | 45.34 | BER | 67% |
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.
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.
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.
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 | |||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode |
200 | 10 | 250 | BER | ||
200 | 10 | 34 | JDC | 120.19 | BER |
200 | 10 | 18 | OB | 58.88 | BER |
200 | 10 | 51 | FFY | 82.42 | BER |
200 | 20 | 320 | BER | ||
200 | 20 | 32 | AOL | 45.34 | BER |
200 | 60 | 500 | AER | ||
200 | 30 | 1084.51 | AER | ||
300 | 10 | 500 | BER | ||
300 | 10 | 34 | JDC | 235.67 | BER |
300 | 10 | 23 | LPT | 456.34 | BER |
300 | 40 | 700 | BER | ||
300 | 40 | 51 | FFY | 340 | BER |
300 | 40 | 45 | KT | 290 | BER |
300 | 40 | 18 | OB | 2345.78 | BER |
300 | 90 | 1000 | AER | ||
Expected Output | |||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode |
200 | 10 | 250 | BER | ||
200 | 10 | 34 | JDC | 120.19 | BER |
200 | 10 | 18 | OB | 58.88 | BER |
200 | 10 | 51 | FFY | 82.42 | BER |
200 | 20 | 320 | BER | ||
200 | 20 | 32 | AOL | 45.34 | BER |
200 | 60 | 500 | AER | ||
200 | 60 | 500 | AER | ||
200 | 60 | 500 | AER | ||
200 | 60 | 500 | AER | ||
200 | 30 | 1084.51 | AER | ||
200 | 30 | 1084.51 | AER | ||
200 | 30 | 1084.51 | AER | ||
200 | 30 | 1084.51 | AER | ||
300 | 10 | 500 | BER | ||
300 | 10 | 34 | JDC | 235.67 | BER |
300 | 10 | 23 | LPT | 456.34 | BER |
300 | 40 | 700 | BER | ||
300 | 40 | 51 | FFY | 340 | BER |
300 | 40 | 45 | KT | 290 | BER |
300 | 40 | 18 | OB | 2345.78 | BER |
300 | 90 | 1000 | AER | ||
300 | 90 | 1000 | AER | ||
300 | 90 | 1000 | AER | ||
300 | 90 | 1000 | AER |
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?
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.
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.
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:
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.
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
Report View Screenshot
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.
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:
I applied replace value function to "InsertRecords" step and add one more step to keep the visual consistent.
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 | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | Percentage |
200 | 10 | 250 | BER | 10% | ||
200 | 10 | 34 | JDC | 120.19 | BER | 15% |
200 | 10 | 18 | OB | 58.88 | BER | 25% |
200 | 10 | 51 | FFY | 82.42 | BER | 13% |
200 | 60 | 500 | AER | 40% | ||
200 | 30 | 1084.51 | AER | 35% | ||
300 | 40 | 700 | BER | 35% | ||
300 | 40 | 51 | FFY | 340 | BER | 45% |
300 | 40 | 45 | KT | 290 | BER | 80% |
300 | 40 | 18 | OB | 2345.78 | BER | 20% |
300 | 90 | 1000 | AER | 10% | ||
Expected Output | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | Percentage |
200 | 10 | 250 | BER | 10% | ||
200 | 10 | 34 | JDC | 120.19 | BER | 15% |
200 | 10 | 18 | OB | 58.88 | BER | 25% |
200 | 10 | 51 | FFY | 82.42 | BER | 13% |
200 | 60 | 500 | AER | 40% | ||
200 | 60 | 34 | JDC | 500 | AER | 15% |
200 | 60 | 18 | OB | 500 | AER | 25% |
200 | 60 | 51 | FFY | 500 | AER | 13% |
200 | 30 | 1084.51 | AER | 35% | ||
200 | 30 | 34 | JDC | 1084.51 | AER | 15% |
200 | 30 | 18 | OB | 1084.51 | AER | 25% |
200 | 30 | 51 | FFY | 1084.51 | AER | 13% |
300 | 40 | 700 | BER | 35% | ||
300 | 40 | 51 | FFY | 340 | BER | 45% |
300 | 40 | 45 | KT | 290 | BER | 80% |
300 | 40 | 18 | OB | 2345.78 | BER | 20% |
300 | 90 | 1000 | AER | 10% | ||
300 | 90 | 51 | FFY | 1000 | AER | 45% |
300 | 90 | 45 | KT | 1000 | AER | 80% |
300 | 90 | 18 | OB | 1000 | AER | 20% |
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