Forum Discussion

amsrivas's avatar
amsrivas
Helper IV
5 years ago

Power BI Data Modelling

Hi Folks!! I have a data as below   ColA ColB ColC Data Data Data L-1 2 3 L-2 5 6 Cloud Cloud Cloud L-3 8 9 L-4 11 12 Azure Azure Azure L-5 14 15 L-...
Need Help
amsrivas's avatar
amsrivas
Helper IV
5 years ago

Hi selimovd 

I can understand this can be done via Pivot/UnPivot however i am not able to avhieve this.

Can you help me out by step by step on how to perform above transformation !!!

 

Thanks

Amit

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey amsrivas ,

     

    you need 2 items, a function for the transformation and the query itself.

    Add the following query in Power Query with the name "TransformTable":

    (x) as table =>
let
    #"Save Header Name" = {x{0}[ColA]},
    #"Add New Column" = Table.AddColumn(x, "Source", each #"Save Header Name"{0} ),
    #"Removed Top Rows" = Table.Skip(#"Add New Column",1),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Top Rows",{"Source", "ColA", "ColB", "ColC"})
in
    #"Reordered Columns"

     

    And then the query itself:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVTSQaFidaKVfHQNgVwjIDaG8kFsUyA2A/Odc/JLU4A8VBqi0hjItwBiSyjfBMg2BBlnaAQWcawqLUoFclFpiFqQDYZgDaZQETMQxxxEWCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}}),
    #"Split Tables" = Table.Split( #"Changed Type", 3 ),
    #"Transform Table" = List.Transform(#"Split Tables", TransformTable),
    #"Load Table" = Table.Combine( #"Transform Table" )
in
    #"Load Table"

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • amsrivas's avatar
      amsrivas
      Helper IV
      5 years ago

      HI selimovd ,

      Solution looks promising and its works with data provided.

      However data which i have provided is dummy one and in actual data it is not necessary that we always have two rows between every heads, for e..g data could be any thing like as below

       

      ColA        ColB         ColC
      Data         Data         Data
      L-1             2              3
      L-2             5              6
      L-3            22            3322
      L-4            231          233
      Cloud       Cloud      Cloud
      L-3             8               9
      L-4            11             12
      L-6            78             121
      Azure       Azure        Azure
      L-5            14             15
      L-6            17              18

       

      - It is not necessary that we have only three heads such as Data, Cloud, Azure etc, it could be any number.

      - Number of columns are not fixed are also not fixed, it could be ColA, ColB, ColC, ColD etc, again it could be any number.

      - Number of rows between two heads can be any, it not neccessarily two, in above data set, there are 4 rows for Data, 3 rows for Cloud etc.

       

      Please suggest!!

      Thanks

      Amit

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        This M code works

        let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([Column1],2)<>"L-" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1]=[Custom] then "Remove" else "keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"})
in
    #"Removed Columns"

        Hope this helps.