Forum Discussion
Solved
Generate row between two tables with criteria
Hello here I have a problem that I would like to submit to you My first table CODIFICATION CODIFICATION 2 NUMERO DOCUMENT DESIGNATION DECOUPAGE PAR PROD DOC LOT EQUIVALENT APPLICA...
Hello GCH_FR
On your second table, you just have to select the 5 first columns and select Unpivot Other ColumnsThen you applied a filter to only return the row with value = 1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYkcgNjE19IBQQBKCY3UgSixhSiwNAyAUkDQAYyxKguFKIMpiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MFZ#(lf)MFZ#(lf)MFZ" = _t, PONT = _t, #"ACR OU VAT" = _t, NUMERO_ACR = _t, ACR_LOT_EQUIVALENT = _t, #"840YST" = _t, #"801YCA" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"MFZ#(lf)MFZ#(lf)MFZ", "PONT", "ACR OU VAT", "NUMERO_ACR", "ACR_LOT_EQUIVALENT"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "1")) in #"Filtered Rows"Then 2 solutions, you click on close an applied and create a relation between your two 2 tables (many to one)
You merge your 2 tables
Let me know if you need more help to fix this issue 🙂
You should filter ACR rows with 840YST = 1 and add a join key to use for cross join, then join Main x ACR and add new NUMERO DOCUMENT by combining
Add updated DESIGNATION and select and rename columns.
= let
Main = Table.FromRecords({
[CODIFICATION="2-CONCEPTION", CODIFICATION_2="SCHÉMAS DRD ACR", NUMERO_DOCUMENT="840YST", DESIGNATION="HVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR", DECOUPAGE_PAR="ACR", PROD_DOC="AXI", LOT_EQUIVALENT=null, APPLICABLE_NAVIRE="X", PRE=null, BPA="RCA", BPE="RLI"]
}),
ACR = Table.FromRecords({
[MFZ=1, PONT=5, ACR_OU_VAT="A", NUMERO_ACR="451H", ACR_LOT_EQUIVALENT="451", #"840YST"=1],
[MFZ=1, PONT=9, ACR_OU_VAT="A", NUMERO_ACR="491P", ACR_LOT_EQUIVALENT="491", #"840YST"=0],
[MFZ=1, PONT=9, ACR_OU_VAT="A", NUMERO_ACR="491S", ACR_LOT_EQUIVALENT="491", #"840YST"=1]
}),
ACR_Filtered = Table.SelectRows(ACR, each Record.Field(_, "840YST") = 1),
MainWithKey = Table.AddColumn(Main, "JoinKey", each 1),
ACRWithKey = Table.AddColumn(ACR_Filtered, "JoinKey", each 1),
Joined = Table.Join(MainWithKey, "JoinKey", ACRWithKey, "JoinKey", JoinKind.Inner),
WithNewDoc = Table.AddColumn(Joined, "NUMERO DOCUMENT FINAL", each [NUMERO_DOCUMENT] & [NUMERO_ACR]),
WithDesignation = Table.AddColumn(WithNewDoc, "DESIGNATION FINAL", each [DESIGNATION] & " " & [NUMERO_ACR]),
Final = Table.SelectColumns(WithDesignation, {
"CODIFICATION_2", "NUMERO DOCUMENT FINAL", "DESIGNATION FINAL", "PROD_DOC", "ACR_LOT_EQUIVALENT", "APPLICABLE_NAVIRE", "PRE", "BPA", "BPE", "DECOUPAGE_PAR"
}),
Renamed = Table.RenameColumns(Final, {
{"CODIFICATION_2", "CODIFICATION 2"},
{"NUMERO DOCUMENT FINAL", "NUMERO DOCUMENT"},
{"DESIGNATION FINAL", "DESIGNATION"},
{"PROD_DOC", "PROD DOC"},
{"ACR_LOT_EQUIVALENT", "LOT EQUIVALENT"},
{"APPLICABLE_NAVIRE", "APPLICABLE NAVIRE"},
{"DECOUPAGE_PAR", "DECOUPAGE PAR"}
})
in
Renamed
thank you for your reply, but perhaps I wasn't specific enough in my request. it's work like a charm with one document but i've got many document like that :
| CODIFICATION | CODIFICATION_2 | NUMERO_DOCUMENT | DESIGNATION | DECOUPAGE_PAR | PROD_DOC | LOT_EQUIVALENT | APPLICABLE_NAVIRE | PRE | BPA | BPE |
| 2-CONCEPTION | SCHÉMAS DRD ACR | 840YST | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR | ACR | AXI | X | RCA | RLI | ||
| 3-PLANS | PLAN ACR CA/PE/CD | 801YCA | HVAC - PLAN DE POSITIONNEMENT DES CARLINGAGES ET SERRURURIE | ACR | AXI | X | RFE |
| MFZ | PONT | ACR OU VAT | NUMERO_ACR | ACR_LOT_EQUIVALENT | 840YST | 801YCA |
| 1 | 5 | A | 451H | 451 | 1 | 1 |
| 1 | 9 | A | 491P | 491 | 0 | 0 |
| 1 | 9 | A | 491S | 491 | 1 | 0 |