Denmark - Power BI User Group

Expand all | Collapse all

Databehandling: Unik værdi og transpose

  • 1.  Databehandling: Unik værdi og transpose

    Posted Jan 04, 2019 03:10 PM
      |   view attached
    Hej,
    (Dette er mit først indlæg, så ret mig gerne hvis dette skal gøres anderledes her i forumet.)

    Jeg bokser med datamanipulering i Power BI Desktop, og håber der er en genial person, der kan pege mig i den rigtige retning.

    Problemstilling består i at tabelnøglen "Dimension ID" ikke er unik, hvorfor jeg ikke kan linke til denne værdi fra andre tabeller.

    Jeg har vedhæftede et simpelt data eksempel, hvor jeg har data, som det bliver udlæst af vores ERP system, samt hvordan jeg håber data kan ende. Eksemplet er vist i Excel, men det er Power BI jeg ønsker at benytte til behandling af data.

    Jeg håber der er en der kan vise mig hvordan jeg laver vedhæftede data ændring.

    Skriv gerne hvis jeg skal uddybe problemstillingen.

    /martin

    ------------------------------
    Martin Malmos Lauritsen
    31774546
    ------------------------------

    Attachment(s)

    xlsx
    dim set entry.xlsx   20K 1 version


  • 2.  RE: Databehandling: Unik værdi og transpose

    Gold Contributor
    Posted Jan 05, 2019 06:20 AM
      |   view attached
    Hi Martin

    That was a fun challenge

    let
    //get the table before transformation will be used later
    Source = Excel.CurrentWorkbook(){[Name="Before"]}[Content],
    //Removed Columns to enable to get unique values
    #"Removed Columns" = Table.RemoveColumns(Source,{"Dimension Name", "Dimension value"}),
    //Get the distinct list of Dimension ID's
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    //Merge this with the full tabel
    #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates",{"Dimension ID"},Source,{"Dimension ID"},"Source",JoinKind.LeftOuter),
    //Added a custom column with the column Dimension value from the nested join
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [Source][Dimension value]),
    //Extract the list items and create a semicolon separated list
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    //remove the nested join column as we dont need it in the result
    #"Removed Columns2" = Table.RemoveColumns(#"Extracted Values",{"Source"}),
    //Split the column with semicolons and name the column with the unique dimension names from the source
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), List.Distinct(Source[Dimension Name]))
    in
    #"Split Column by Delimiter"


    Here is a proposed solution - it should perform ok even with many records.

    Let us know if it works

    Power Query On !


    ------------------------------
    Erik Svensen
    PUG Leader
    Denmark
    es@catmansolution.com
    ------------------------------

    Attachment(s)



  • 3.  RE: Databehandling: Unik værdi og transpose

    Silver Contributor
    Posted Jan 08, 2019 01:41 PM
      |   view attached
    Hello from France,

    Sorry I don't speak Danish but from what I understand, a simple "Pivot" could make the job here, nop ?

    Attached my proposal and below the M Code:

    let
    // Get the table before transformation will be used later
    Source = Excel.CurrentWorkbook(){[Name="Before"]}[Content],
    // Pivoting Dimension Names by "Not Aggregating" Dimension Values
    Pivot_Dimension_Name = Table.Pivot(Source, List.Distinct(Source[#"Dimension Name"]), "Dimension Name", "Dimension value")
    in
    Pivot_Dimension_Name

    ------------------------------
    Tristan Malherbe
    French Power BI User Group Leader

    ------------------------------

    Attachment(s)