Flat file from multiple hierarchy in rows

Power Query
1

Hello.

I am trying to create a flat file to input in PowerBI.
I have attached the excel file with sample data.
In the first section I report the structure of my file. As you can see I have a number of KPIs for Cities.
Such KPIs are clustered into different level (Lev1-4) and are in rows.

I would like to end up with a flatfile as I tried to perform manually in the second part of the excel file.
Each KPI for the specific city is part of a general cluster (Infrastructure) and are then further clustered into different categories, always belonging to the Infrastructure level.

Is it possible to create a proper flatfile?

Thank you very much for your help!
Best,
Marco

How to flat.xlsx (12.1 KB)

2

Hi @marcoercolani,

Usually the technique for transforming a table with multiple headers is:

  1. Make sure all headers are in the rows section of the table, not in the header.
  2. Transpose table
  3. Select all header columns, Transform → “Merge columns” with a delimiter
  4. Transpose the table back
  5. Use the first row as header
  6. Select all label (or “row header”) columns,
  7. Transform → Unpivot columns → Unpivot other columns
  8. Split “Attribute” column by delimiter
  9. Set appropriate data types and Rename columns

.
Paste this script into a new blank query, to see the result for your supplied sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZBBa8MwDIX/ysi5h8ZL3OTawqBsg9KWXUoOSuMVQ2oP1Tm0v75+xmGk0CW3QfSeIjmf5BwOSTL7fdbmm+niuDu6jtV/FarZcKslW2pqMs3EfM9kLj+W3aT8cdqKXDowMbAnPVBW2l39+1adtDVo2M44Rmmnb7jn+2b9kkYX0V+jZ9Hz6DL6InoRRnzqloz1pQ97rokbTfh9jlpMWeqTVwliAckhAkCJqaLwgjEiC6wvZfRR/c3yAUgZmKLPAliWoQnS1p5HOGEszkPkHDQfKXYrkRV5AL21lpUZWSrDV4t5L0CXWDTFJUPIpKru", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    TransposeTable = Table.Transpose(Source),
    MergeColumns = Table.CombineColumns(TransposeTable,{"Column1", "Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    TransposeBack = Table.Transpose(MergeColumns),
    CleanHeaderNames = Table.ReplaceValue(TransposeBack,";;;","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4"}),
    PromoteHeaders = Table.PromoteHeaders(CleanHeaderNames, [PromoteAllScalars=true]),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(PromoteHeaders, {"City", "Region", "Country", "Size"}, "Level", "Value"),
    SplitByDelimiter = Table.SplitColumn(UnpivotOtherColumns, "Level", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4"})
in
    SplitByDelimiter

I hope this is helpful

2 Likes
3

He Melissa.
Wow, great, thank you very much!!
Worked perfectly and understood the logic.

Thank you,
Marco