Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sylvine_Wyz
Helper IV
Helper IV

Liste de donnée - dupliquer les lignes

Bonjour,

j'ai besoin de votre aide.

Ma table dispose de 3 colonnes : Fullsize, Brand, EanCode

FullSizeBrandEanCode
205/55 R 16Continental35645671819
205/55 R 16Continental15614881789
205/55 R 16Michelin15489174861
205/55 R 16Michelin35645671819


Je veux faire ressortir un tableau : 
En ligne : Fullsize (autant de ligne fullsize que d'EanCode)
En colonne : Brand
En valeur : EanCode (qui multiplie les lignes si plusieurs EanCode par Brand)

FullSizeMichelinContinental
205/55 R 161548917486135645671819
205/55 R 163564567181915614881789



Actuellement j'ai réussi seulement à concaténer les valeurs par colonne : 

Sylvine_Wyz_0-1686296934682.png

Avec cette formule : 

Liste des valeurs de FullSize =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('wyz_reporting bi_tyre'[EanCode])
VAR __MAX_VALUES_TO_SHOW = 10
RETURN
    IF(
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    VALUES('wyz_reporting bi_tyre'[EanCode]),
                    'wyz_reporting bi_tyre'[EanCode],
                    ASC
                ),
                'wyz_reporting bi_tyre'[EanCode],
                ", ",
                'wyz_reporting bi_tyre'[EanCode],
                ASC
            ),
            ",  etc."
        ),
        CONCATENATEX(
            VALUES('wyz_reporting bi_tyre'[EanCode]),
            'wyz_reporting bi_tyre'[EanCode],
            ", ",
            'wyz_reporting bi_tyre'[EanCode],
            ASC
        )
    )

Mais elle ne me convient pas.

Merci par avance pour votre aide.

Have a nice day
1 ACCEPTED SOLUTION

what you woud need to do is add an index columns that increments per Brand and then use that index in your visual rows.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIw1Tc1VQhSMDRT0lFyzs8rycxLzStJzAHyjE3NTEzNzA0tDC2VYnXwqzU0NTM0sbAwNLfAptY3MzkjNSczD6zQxMLS0NzEwswQv0IU22MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FullSize = _t, Brand = _t, EanCode = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Brand", "FullSize"}, {{"Rows", each _, type table [FullSize=nullable text, Brand=nullable text, EanCode=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows], "Index", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"FullSize", "Brand", "EanCode", "Index"}, {"FullSize", "Brand", "EanCode", "Index"})
in
    #"Expanded Custom"

 

lbendlin_0-1686661932756.png

 

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

If these two products really have the same EAN then your request is ambiguous and cannot be implemented.

@lbendlin d'accord merci du retour.
Dans ce cas avec des données uniques comme celles ci, est ce possible et comment ?

Sylvine_Wyz_0-1686572456686.png

 

Still no.  You need columns that uniquely identify each tire size.  FullSize has all the same values.

Hello @lbendlin,
c'est normal d'avoir les mêmes données FullSize. La valeur unique est la concaténation de EanCode, Brand et FullSize.
Ce qui nous intéresse c'est d'arriver à sortir ce tableau : 

FullSizeBRIDGESTONECONTINENTAL
205/55 R 16 91V32863402916204019238456080
205/55 R 16 91V32863426155164019238339802
205/55 R 16 91V3286340291613 
205/55 R 16 91V3286340306416 

Sylvine_Wyz_0-1686658721995.png

 

what you woud need to do is add an index columns that increments per Brand and then use that index in your visual rows.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIw1Tc1VQhSMDRT0lFyzs8rycxLzStJzAHyjE3NTEzNzA0tDC2VYnXwqzU0NTM0sbAwNLfAptY3MzkjNSczD6zQxMLS0NzEwswQv0IU22MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FullSize = _t, Brand = _t, EanCode = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Brand", "FullSize"}, {{"Rows", each _, type table [FullSize=nullable text, Brand=nullable text, EanCode=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows], "Index", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"FullSize", "Brand", "EanCode", "Index"}, {"FullSize", "Brand", "EanCode", "Index"})
in
    #"Expanded Custom"

 

lbendlin_0-1686661932756.png

 

@lbendlin Merci ça m'aide.
mais à la place de la colonne index dans le tableau je dois avoir FullSize, pour que les utilisateurs puissent filtrer sur la dimension

Sylvine_Wyz_0-1686670213460.png

thanks for your help  !

lbendlin
Super User
Super User

Your sample data has the same EAN code for two of the tires.  I don't think that is accurate.

Bonjour @lbendlin,

C'est correct et c'est pourquoi nous avons besoin de toute la liste d'EAN par Brand en colonne et par FullSize en ligne.

Merci pour votre aide

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.