Generate row between two tables with criteria

[GCH_FR]

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	APPLICABLE NAVIRE	PRE	BPA	BPE
2-CONCEPTION	SCHÉMAS DRD ACR	840YST	HVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR	ACR	AXI		X		RCA	RLI

My second Table "ACR" is the table in reference inside the first table "Decoupage Par" : 

MFZ	PONT	ACR OU VAT	NUMERO ACR	LOT EQUIVALENT	840YST
1	5	A	451H	451	1
1	9	A	491P	491	0
1	9	A	491S	491	1

the expected result is this 

NUMERO DOCUMENT = [Table 1]{NUMERO DOCUMENT} & [Table 2]{NUMERO ACR} if there is a 1 in the column "840YST"

CODIFICATION 2	NUMERO DOCUMENT	DESIGNATION	PROD DOC	LOT EQUIVALENT	APPLICABLE NAVIRE	PRE	BPA	BPE	DECOUPAGE PAR
SCHÉMAS DRD ACR	840YST451H	HVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR 451H	AXI	451	X		RLI	RIP	ACR
SCHÉMAS DRD ACR	840YST491S	HVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR 491S	AXI	491	X		RLI	RIP	ACR

How can I manage lines according to the “1” criterion in 840YST?

I don't know if I've made myself clear in my request, so please don't hesitate to ask me for further information if you need it.

Thanks a lot in advance for your help

[Cookistador · Accepted solution]

Hello @GCH_FR 
On your second table, you just have to select the 5 first columns and select Unpivot Other Columns

Then 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 🙂

[AmiraBedh]

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

[GCH_FR]

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

[Cookistador]

Two split tables?

I guess you can have many types of documents? 
E.g 850ZTU ?

Or you only have one type of doc?

If you have many of them, does it means that you will have many columns? 

So in your table ACR, you could have a column called 850ZTU  ?

If it is not the case, I would replace the 1 in criterion in 840YST, by 840YST and created a calculated column

Then Linked my two table in Power BI