Forum Discussion

EDS's avatar
EDS
Icon for Helper I rankHelper I
5 years ago

COLUMNA

Buenos días, soy nuevo en Powerbi, y tengo una duda.

Dispongo de una tabla con dos columnas, una el codigo de cliente y otro el% de descuento y el concepto

La mayoria de los clientes tiene solo un% de descuento, pero hay casos en los que un cliente puede tener dos% de descuento, los cuales se aplican en cascada.

Por ejplo

cliente dcto concepto

1 5% de rappel

2 4% Pronto pago

3 2% Rappel

1 2% Pronto pago.

 

Como puede crear una tabla en la que aparezcan dos columnas, en la que por ejplo para el cliente 1 aparece una columna con el dcto del 5% y otra columna en la que aparece el 2%.

 

Gracias.

Need Help

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    EDS 

    I used Power Query to create a solution. Paste the code given below on the Advanced Editor of a New Blank Query and check the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVBRJBiQUFqTlKsTrRSkZArglILDg/P08hILEyNzWvBCxjDBQ0QlNtCBMLKMrPLSiBqdeDazBB1hALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cutomer = _t, Discount = _t, Concept = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cutomer", Int64.Type}, {"Discount", Percentage.Type}, {"Concept", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cutomer"}, {{"Count", each _, type table [Cutomer=nullable number, Discount=nullable number, Concept=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Discount 1", each [Count][Discount]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Discount 1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Discount 1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Discount 1.1", "Discount 1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Discount 1.1", type number}, {"Discount 1.2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

    Data:

     




    Output:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi EDS 

    The data you provided is not very clear, under what circumstances the customer’s discount percentage is 5% and when is 2% ? What is your formula for calculating this result ?

    Could you provide us with some data, formulas or samples ?

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.