Data model conversion-Power Query

Question

Hello,all.I have encountered a problem, as shown in the photo.&nbsp;I got some data (Figure 1) and want to convert it to the form of Figure 2.I have entered Power Query through the From Table（Figure 3), but don't know what to do next.&nbsp;(Figure 1)&nbsp;(Figure 2)&nbsp;(Figure 3)&nbsp;Maybe you can give me some suggestions or a way to learn.Any help would be greatly appreciated.Thanks.

peterbartholomew1 · Answer

Victoria-9&nbsp;I do not think tables or PQ are really intended to deal with two levels of heading.&nbsp; If you treat the J and K columns as two separate tables (this can be achieved within PQ by deleting columns). Then unpivot each using 'other columns' with Number, Name and Group selected to get two tables of the form you require and save each as a connection only.&nbsp;Then, starting with the first table, merge the second using&nbsp;Number, Name, Group and Month from each as key fields for the join.&nbsp; Finally expand the second table extracting the value field only.

victoria-9 · Answer

PeterBartholomew1&nbsp;Thanks for the way you provide, I will try it. Before, I saw a way to process this data using PQ in the same table, perhaps changing the title form, but I forgot,I will continue to look for.Thanks very much for your help.

peterbartholomew1 · Answer

Victoria-9&nbsp;I think I have found A way of doing what you want without splitting the table into two.&nbsp; I won't claim it is THE way because I am just working by trial and error!If you create single level headers by manually concatenating the J/K with the month (a space will do as a separator) and then unpivot, you get a table twice as long as you want.&nbsp; If you then split the compound column into its letter and date parts, you can (re)pivot on the new letter column to give the J and K output in separate columns.I am not sure whether this helps you but I am learning!

sergeibaklan · Answer

Hi Victoria-9 ,
&nbsp;
Initial idea of how to work with multi-row headers is from Gil Raviv, at least I picked-up it here. Now don't remember there are the details, in his book or blog. In brief, transformation is like

generated script is
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MergeFirstThreeColumns = Table.CombineColumns(Source,
        {"Column1", "Column2", "Column3"},
        Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged"),
    TransposeTable = Table.Transpose(MergeFirstThreeColumns),
    FirstRowAsHeader = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true]),
    UnpivoteOtherThanFirstTwoColumns = Table.UnpivotOtherColumns(FirstRowAsHeader,
        {"==", "Number=Name=Group"}, "Attribute", "Value"),
    PivotMonths = Table.Pivot(UnpivoteOtherThanFirstTwoColumns,
        List.Distinct(UnpivoteOtherThanFirstTwoColumns[#"Number=Name=Group"]),
        "Number=Name=Group", "Value"),
    SplitFirstColumnsBack = Table.SplitColumn(PivotMonths, "Attribute",
        Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    RenameAsBefore = Table.RenameColumns(SplitFirstColumnsBack,
        {{"==", "  "}, {"Attribute.1", "Number"}, {"Attribute.2", "Name"}, {"Attribute.3", "Group"}}),
    ProperColumnsOrder = Table.ReorderColumns(RenameAsBefore,{"Number", "Name", "Group", "J", "K", "  "})
in
    ProperColumnsOrder
and file is attached.
Hope above are self-explained if repeat step by step. Didn't play with final sorting, recognised too late it's better to do one.

victoria-9 · Answer

Hi SergeiBaklan&nbsp;Thank you very much!I followed your steps to change the information and tried it. I have succeeded! But I made a mistake in my question.The value contained in the 'Number' column should be ‘123456’ instead of ‘a123’. After replacing the ‘number’ with ‘letter + number’ in my table , I succeeded.Do you know how to modify the step if the number column is all numeric?&nbsp;

Forum Discussion

Data model conversion-Power Query

7 Replies

Resources