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.
How to insert the same number of rows for Mode=AER in comparision to Mode=BER for that respective "ID" (here in this case "200" & "300") and also to copy the previous column data for ID, Assignment, AssignmentDesc?
(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 | 34 | JDC | 120.19 | BER |
200 | 10 | 18 | OB | 58.88 | BER |
200 | 10 | 51 | FFY | 82.42 | BER |
200 | 30 | 1084.51 | AER | ||
300 | 10 | 34 | JDC | 235.67 | BER |
300 | 10 | 23 | LPT | 456.34 | BER |
300 | 10 | 45 | APT | 700 | BER |
300 | 10 | 18 | OB | 2345.78 | BER |
300 | 90 | 1000 | AER | ||
Expected Output | |||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode |
200 | 10 | 34 | JDC | 120.19 | BER |
200 | 10 | 18 | OB | 58.88 | BER |
200 | 10 | 51 | FFY | 82.42 | BER |
200 | 30 | 1084.51 | AER | ||
200 | 30 | 34 | JDC | AER | |
200 | 30 | 18 | OB | AER | |
200 | 30 | 51 | FFY | AER | |
300 | 10 | 34 | JDC | 235.67 | BER |
300 | 10 | 23 | LPT | 456.34 | BER |
300 | 10 | 45 | APT | 700 | BER |
300 | 10 | 18 | OB | 2345.78 | BER |
300 | 90 | 1000 | AER | ||
300 | 90 | 34 | JDC | AER | |
300 | 90 | 23 | LPT | AER | |
300 | 90 | 45 | APT | AER | |
300 | 90 | 18 | OB | AER |
Thank you,
Joyhy
Solved! Go to Solution.
Use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCsIwDEbfpdcjNH9rdunUXYigiDcy9v6vYRoFKyuUQxNO2i/rmijnNCSsYHFcTsdaUwac/DKfH2kb/jQ0x212qIFZX1J0LMvLaQRCO4srPgezCcTA4atwNxSxwlialxqN2HG9P52iI8RURxOtv4RWottxfvsRi0KxnTa12aMTwbc3", 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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Temp", each _, type table [ID=nullable number, Code=nullable number, Assignment=nullable number, AssignmentDesc=nullable text, Cost=nullable number, Mode=nullable text]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Filtered Rows" = Table.SelectRows(Tbl, each ([Mode] = "BER")),
Custom2 = Table.ReplaceValue(#"Filtered Rows",each [Code],each null,Replacer.ReplaceValue,{"Code"}),
Custom3 = Table.ReplaceValue(Custom2,each [Cost],each null,Replacer.ReplaceValue,{"Cost"}),
Custom4 = Table.ReplaceValue(Custom3,each [Mode],each null,Replacer.ReplaceValue,{"Mode"}),
#"Appended Query" = Table.Combine({Tbl, Custom4}),
#"Filled Down" = Table.FillDown(#"Appended Query",{"Code", "Mode"})
in
#"Filled Down",
//Function End
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "Temp"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}, {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"})
in
#"Expanded Custom"
Hi,
I am trying to use your given logic on a table which has nearly 50K rows but ran into issues on some of the scenarios which I have not outlined in my earlier post. Now I have updated those scenarios and can you please help me on this. (Expecting output similar to the one outlined in the chart below under the heading "Expected output" ). Please see the chart below for more info.
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, AllCost column values from their copied/previous rows. Here ALLCost is a "calculated column".
3) Newly inserted rows for column Mode="AER" should have the existing values as it is for ID, Code, Mode columns and cost value as null/blank.
Current Data | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | AllCost |
200 | 10 | 250 | BER | 285 | ||
200 | 10 | 34 | JDC | 120.19 | BER | 145.19 |
200 | 10 | 18 | OB | 58.88 | BER | 65 |
200 | 10 | 51 | FFY | 82.42 | BER | 89 |
200 | 20 | 320 | BER | 340 | ||
200 | 20 | 32 | AOL | 45.34 | BER | 78 |
200 | 30 | 1084.51 | AER | 2000 | ||
300 | 10 | 500 | BER | 560 | ||
300 | 10 | 34 | JDC | 235.67 | BER | 300 |
300 | 10 | 23 | LPT | 456.34 | BER | 500 |
300 | 40 | 700 | BER | 756 | ||
300 | 40 | 51 | FFY | 340 | BER | 370 |
300 | 40 | 45 | KT | 290 | BER | 300 |
300 | 40 | 18 | OB | 2345.78 | BER | 2587 |
300 | 90 | 1000 | AER | 1200 | ||
Expected Output | ||||||
ID | Code | Assignment | AssignmentDesc | Cost | Mode | AllCost |
200 | 10 | 250 | BER | 285 | ||
200 | 10 | 34 | JDC | 120.19 | BER | 145.19 |
200 | 10 | 18 | OB | 58.88 | BER | 65 |
200 | 10 | 51 | FFY | 82.42 | BER | 89 |
200 | 20 | 320 | BER | 340 | ||
200 | 20 | 32 | AOL | 45.34 | BER | 78 |
200 | 30 | 1084.51 | AER | 2000 | ||
200 | 30 | 34 | JDC | AER | 145.19 | |
200 | 30 | 18 | OB | AER | 65 | |
200 | 30 | 51 | FFY | AER | 89 | |
300 | 10 | 500 | BER | 560 | ||
300 | 10 | 34 | JDC | 235.67 | BER | 300 |
300 | 10 | 23 | LPT | 456.34 | BER | 500 |
300 | 40 | 700 | BER | 756 | ||
300 | 40 | 51 | FFY | 340 | BER | 370 |
300 | 40 | 45 | KT | 290 | BER | 300 |
300 | 40 | 18 | OB | 2345.78 | BER | 2587 |
300 | 90 | 1000 | AER | 1200 | ||
300 | 90 | 51 | FFY | AER | 370 | |
300 | 90 | 45 | KT | AER | 300 | |
300 | 90 | 18 | OB | AER | 2587 |
try this:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("pdK9CsIwEADgd7m5hPy2abb+WFAURVxEHUSLCLUOsZP47ja1lKBBrW5JyH133N3qCsMUFMXYg+S8z0GR+hRpfTyUp7y8gGLcvqe53oGCUZqACdD1B0IxIqEHkyYc4sEcbt5HlkgHO43XFcZb3NlCIin70oI46CxbdqqkiNNPKntSy6ooXt3Ha9sILDkyyVs4smD2R4MpE8gPnPW+YSlzsOPZomO58JHJ3Y/lwsFGFhuY2H7md7tAGRcocG+DhYc/zM0quRna5g4=", BinaryEncoding.Base64),Compression.Deflate))),
rows = Table.ToRows(Source),
acc = List.Accumulate(rows,
{{}, {}},
(s,c)=>if c{5}<>"AER" then
{s{0}&{List.ReplaceRange(c, 4, 2, {null, "AER"})}, s{1}&{c}}
else
{{}, s{1}&{c}&s{0}}),
result = #table(Table.ColumnNames(Source), acc{1})
in
result
Hi,
Rows are inserting accordingly,But value for the column "MODE" is replaced/copied by its previous row value.In our case it should not.
Please see the chart below for more info.
ID | Code | Assignment | AssignmentDesc | Cost | Mode |
200 | 10 | 18 | OB | 58.88 | BER |
200 | 10 | 34 | JDC | 120.19 | BER |
200 | 10 | 51 | FFY | 82.42 | BER |
200 | 30 | 1084.51 | AER | ||
200 | 30 | 18 | OB | BER—AER Instead of “BER” | |
200 | 30 | 34 | JDC | BER—AER Instead of “BER” | |
200 | 30 | 51 | FFY | BER—AER Instead of “BER” | |
300 | 10 | 18 | OB | 2345.78 | BER |
300 | 10 | 23 | LPT | 456.34 | BER |
300 | 10 | 34 | JDC | 235.67 | BER |
300 | 10 | 45 | APT | 700 | BER |
300 | 90 | 1000 | AER | ||
300 | 90 | 18 | OB | BER—AER Instead of “BER” | |
300 | 90 | 23 | LPT | BER—AER Instead of “BER” | |
300 | 90 | 34 | JDC | BER—AER Instead of “BER” | |
300 | 90 | 45 | APT | BER—AER Instead of “BER” |
Thanks,
Joyhy
Use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCsIwDEbfpdcjNH9rdunUXYigiDcy9v6vYRoFKyuUQxNO2i/rmijnNCSsYHFcTsdaUwac/DKfH2kb/jQ0x212qIFZX1J0LMvLaQRCO4srPgezCcTA4atwNxSxwlialxqN2HG9P52iI8RURxOtv4RWottxfvsRi0KxnTa12aMTwbc3", 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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Temp", each _, type table [ID=nullable number, Code=nullable number, Assignment=nullable number, AssignmentDesc=nullable text, Cost=nullable number, Mode=nullable text]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Filtered Rows" = Table.SelectRows(Tbl, each ([Mode] = "BER")),
Custom2 = Table.ReplaceValue(#"Filtered Rows",each [Code],each null,Replacer.ReplaceValue,{"Code"}),
Custom3 = Table.ReplaceValue(Custom2,each [Cost],each null,Replacer.ReplaceValue,{"Cost"}),
Custom4 = Table.ReplaceValue(Custom3,each [Mode],each null,Replacer.ReplaceValue,{"Mode"}),
#"Appended Query" = Table.Combine({Tbl, Custom4}),
#"Filled Down" = Table.FillDown(#"Appended Query",{"Code", "Mode"})
in
#"Filled Down",
//Function End
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "Temp"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}, {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"})
in
#"Expanded Custom"
Thank you for your help!
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCsIwDEbfpdcjNH9rdunUXYigiDcy9v6vYRoFKyuUQxNO2i/rmijnNCSsYHFcTsdaUwac/DKfH2kb/jQ0x212qIFZX1J0LMvLaQRCO4srPgezCcTA4atwNxSxwlialxqN2HG9P52iI8RURxOtv4RWottxfvsRi0KxnTa12aMTwbc3", 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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Temp", each _, type table [ID=nullable number, Code=nullable number, Assignment=nullable number, AssignmentDesc=nullable text, Cost=nullable number, Mode=nullable text]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Filtered Rows" = Table.SelectRows(Tbl, each ([Mode] = "BER")),
Custom2 = Table.ReplaceValue(#"Filtered Rows",each [Code],each null,Replacer.ReplaceValue,{"Code"}),
Custom3 = Table.ReplaceValue(Custom2,each [Cost],each null,Replacer.ReplaceValue,{"Cost"}),
#"Appended Query" = Table.Combine({Tbl, Custom3}),
#"Filled Down" = Table.FillDown(#"Appended Query",{"Code"})
in
#"Filled Down",
//Function End
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "Temp"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}, {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"})
in
#"Expanded Custom"
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.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |