Hello here I have a problem that I would like to submit to you My first table CODIFICATIONCODIFICATION 2NUMERO DOCUMENTDESIGNATIONDECOUPAGE PARPROD DOCLOT EQUIVALENTAPPLICABLE NAVIREPREBPABPE2-CONCEPTIONSCHÉMAS DRD ACR840YSTHVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR ACRAXI X RCARLI My second Table "ACR" is the table in reference inside the first table "Decoupage Par" : MFZPONTACR OU VATNUMERO ACRLOT EQUIVALENT840YST15A451H451119A491P491019A491S4911 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 2NUMERO DOCUMENTDESIGNATIONPROD DOCLOT EQUIVALENTAPPLICABLE NAVIREPREBPABPEDECOUPAGE PARSCHÉMAS DRD ACR840YST451HHVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR 451HAXI451X RLIRIPACRSCHÉMAS DRD ACR840YST491SHVAC - SCHEMAS GEOGRAPHIQUES - DRD - ACR 491SAXI491X RLIRIPACR 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

GCH_FR

Helper I

1 year ago

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

Need Help

Cookistador

1 year ago

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 🙂

4 Replies

AmiraBedh

Super User

1 year ago

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

Generate row between two tables with criteria.pbix31 KB

GCH_FR

Helper I

1 year ago

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

Super User

1 year ago

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 🙂

Cookistador
Super User
1 year ago
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

Forum Discussion

Generate row between two tables with criteria

4 Replies

Related content

Generating Missing Data

Sequential Order column based on multiple criteria

Generating link

DAX FILTER with multiple criteria

Generate row based on column criteria