Home

Data model conversion-Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-376622%22%20slang%3D%22en-US%22%3EData%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376622%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2Call.%3C%2FP%3E%3CP%3EI%20have%20encountered%20a%20problem%2C%20as%20shown%20in%20the%20photo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20some%20data%20(Figure%201)%20and%20want%20to%20convert%20it%20to%20the%20form%20of%20Figure%202.I%20have%20entered%20Power%20Query%20through%20the%20From%20Table%EF%BC%88Figure%203)%2C%20but%20don't%20know%20what%20to%20do%20next.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20490px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F93639i4764360E12CA7502%2Fimage-dimensions%2F490x168%3Fv%3D1.0%22%20width%3D%22490%22%20height%3D%22168%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E(Figure%201)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20354px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F93638iEA5036930C9BB8F0%2Fimage-dimensions%2F354x386%3Fv%3D1.0%22%20width%3D%22354%22%20height%3D%22386%22%20alt%3D%222.PNG%22%20title%3D%222.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CI%3E(Figure%202)%3C%2FI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F93637i106E4700CF09986F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%223.PNG%22%20title%3D%223.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CI%3E(Figure%203)%3C%2FI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20you%20can%20give%20me%20some%20suggestions%20or%20a%20way%20to%20learn.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-376622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386636%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303053%22%20target%3D%22_blank%22%3E%40Victoria-9%3C%2FA%3E%26nbsp%3B%2C%20in%20addition%20I%20added%20months%20sorting%20by%20adding%20month%20number%20in%20front%20of%20each%20its%20name%20before%20pivoting%3B%20removed%20that%20index%20on%20latest%20steps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386619%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386619%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303053%22%20target%3D%22_blank%22%3E%40Victoria-9%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Power%20Query%20editor%20you%20may%20convert%20column%20%5BNumber%5D%20to%20type%20text%20on%20one%20of%20the%20first%20steps%2C%20and%20on%20one%20of%20the%20last%20one%20convert%20it%20back%20to%20Whole%20Number%2C%20please%20see%20attached.%20Columns%20combiner%20works%20with%20texts%20only.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386614%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386614%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3EI%20followed%20your%20steps%20to%20change%20the%20information%20and%20tried%20it.%20I%20have%20succeeded!%20But%20I%20made%20a%20mistake%20in%20my%20question.%3C%2FP%3E%3CP%3EThe%20value%20contained%20in%20the%20'Number'%20column%20should%20be%20%E2%80%98123456%E2%80%99%20instead%20of%20%E2%80%98a123%E2%80%99.%20After%20replacing%20the%20%E2%80%98number%E2%80%99%20with%20%E2%80%98letter%20%2B%20number%E2%80%99%20in%20my%20table%20%2C%20I%20succeeded.%3C%2FP%3E%3CP%3EDo%20you%20know%20how%20to%20modify%20the%20step%20if%20the%20number%20column%20is%20all%20numeric%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-385656%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-385656%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303053%22%20target%3D%22_blank%22%3E%40Victoria-9%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInitial%20idea%20of%20how%20to%20work%20with%20multi-row%20headers%20is%20from%20Gil%20Raviv%2C%20at%20least%20I%20picked-up%20it%20here.%20Now%20don't%20remember%20there%20are%20the%20details%2C%20in%20his%20book%20or%20blog.%20In%20brief%2C%20transformation%20is%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20539px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F98667i0C9066C1AE79A342%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Egenerated%20script%20is%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table2%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20MergeFirstThreeColumns%20%3D%20Table.CombineColumns(Source%2C%0A%20%20%20%20%20%20%20%20%7B%22Column1%22%2C%20%22Column2%22%2C%20%22Column3%22%7D%2C%0A%20%20%20%20%20%20%20%20Combiner.CombineTextByDelimiter(%22%3D%22%2C%20QuoteStyle.None)%2C%22Merged%22)%2C%0A%20%20%20%20TransposeTable%20%3D%20Table.Transpose(MergeFirstThreeColumns)%2C%0A%20%20%20%20FirstRowAsHeader%20%3D%20Table.PromoteHeaders(TransposeTable%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20UnpivoteOtherThanFirstTwoColumns%20%3D%20Table.UnpivotOtherColumns(FirstRowAsHeader%2C%0A%20%20%20%20%20%20%20%20%7B%22%3D%3D%22%2C%20%22Number%3DName%3DGroup%22%7D%2C%20%22Attribute%22%2C%20%22Value%22)%2C%0A%20%20%20%20PivotMonths%20%3D%20Table.Pivot(UnpivoteOtherThanFirstTwoColumns%2C%0A%20%20%20%20%20%20%20%20List.Distinct(UnpivoteOtherThanFirstTwoColumns%5B%23%22Number%3DName%3DGroup%22%5D)%2C%0A%20%20%20%20%20%20%20%20%22Number%3DName%3DGroup%22%2C%20%22Value%22)%2C%0A%20%20%20%20SplitFirstColumnsBack%20%3D%20Table.SplitColumn(PivotMonths%2C%20%22Attribute%22%2C%0A%20%20%20%20%20%20%20%20Splitter.SplitTextByDelimiter(%22%3D%22%2C%20QuoteStyle.Csv)%2C%20%7B%22Attribute.1%22%2C%20%22Attribute.2%22%2C%20%22Attribute.3%22%7D)%2C%0A%20%20%20%20RenameAsBefore%20%3D%20Table.RenameColumns(SplitFirstColumnsBack%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22%3D%3D%22%2C%20%22%20%20%22%7D%2C%20%7B%22Attribute.1%22%2C%20%22Number%22%7D%2C%20%7B%22Attribute.2%22%2C%20%22Name%22%7D%2C%20%7B%22Attribute.3%22%2C%20%22Group%22%7D%7D)%2C%0A%20%20%20%20ProperColumnsOrder%20%3D%20Table.ReorderColumns(RenameAsBefore%2C%7B%22Number%22%2C%20%22Name%22%2C%20%22Group%22%2C%20%22J%22%2C%20%22K%22%2C%20%22%20%20%22%7D)%0Ain%0A%20%20%20%20ProperColumnsOrder%3C%2FPRE%3E%0A%3CP%3Eand%20file%20is%20attached.%3C%2FP%3E%0A%3CP%3EHope%20above%20are%20self-explained%20if%20repeat%20step%20by%20step.%20Didn't%20play%20with%20final%20sorting%2C%20recognised%20too%20late%20it's%20better%20to%20do%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-377587%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377587%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303053%22%20target%3D%22_blank%22%3E%40Victoria-9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20have%20found%20%3CSTRONG%3EA%3C%2FSTRONG%3E%20way%20of%20doing%20what%20you%20want%20without%20splitting%20the%20table%20into%20two.%26nbsp%3B%20I%20won't%20claim%20it%20is%20%3CSTRONG%3ETHE%3C%2FSTRONG%3E%20way%20because%20I%20am%20just%20working%20by%20trial%20and%20error!%3C%2FP%3E%3CP%3EIf%20you%20create%20single%20level%20headers%20by%20manually%20concatenating%20the%20J%2FK%20with%20the%20month%20(a%20space%20will%20do%20as%20a%20separator)%20and%20then%20unpivot%2C%20you%20get%20a%20table%20twice%20as%20long%20as%20you%20want.%26nbsp%3B%20If%20you%20then%20split%20the%20compound%20column%20into%20its%20letter%20and%20date%20parts%2C%20you%20can%20(re)pivot%20on%20the%20new%20letter%20column%20to%20give%20the%20J%20and%20K%20output%20in%20separate%20columns.%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20whether%20this%20helps%20you%20but%20I%20am%20learning!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-377395%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377395%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20way%20you%20provide%2C%20I%20will%20try%20it.%20Before%2C%20I%20saw%20a%20way%20to%20process%20this%20data%20using%20PQ%20in%20the%20same%20table%2C%20perhaps%20changing%20the%20title%20form%2C%20but%20I%20forgot%2CI%20will%20continue%20to%20look%20for.%3C%2FP%3E%3CP%3EThanks%20very%20much%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376833%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20model%20conversion-Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303053%22%20target%3D%22_blank%22%3E%40Victoria-9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20think%20tables%20or%20PQ%20are%20really%20intended%20to%20deal%20with%20two%20levels%20of%20heading.%26nbsp%3B%20If%20you%20treat%20the%20J%20and%20K%20columns%20as%20two%20separate%20tables%20(this%20can%20be%20achieved%20within%20PQ%20by%20deleting%20columns).%20Then%20unpivot%20each%20using%20'other%20columns'%20with%20Number%2C%20Name%20and%20Group%20selected%20to%20get%20two%20tables%20of%20the%20form%20you%20require%20and%20save%20each%20as%20a%20connection%20only.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20starting%20with%20the%20first%20table%2C%20merge%20the%20second%20using%26nbsp%3B%3CSPAN%3ENumber%2C%20Name%2C%20Group%20%3CSPAN%3Eand%20Month%20from%20each%20as%20key%20fields%20for%20the%20join.%26nbsp%3B%20Finally%20expand%20the%20second%20table%20extracting%20the%20value%20field%20only.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Victoria-9
New Contributor

Hello,all.

I have encountered a problem, as shown in the photo.

 

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.

 

1.PNG

(Figure 1)

 

2.PNG

(Figure 2)

 

3.PNG

(Figure 3)

 

Maybe you can give me some suggestions or a way to learn.

Any help would be greatly appreciated.

Thanks.

7 Replies

@Victoria-9 

I do not think tables or PQ are really intended to deal with two levels of heading.  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. 

Then, starting with the first table, merge the second using Number, Name, Group and Month from each as key fields for the join.  Finally expand the second table extracting the value field only.

@Peter Bartholomew 

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.

@Victoria-9 

I think I have found A way of doing what you want without splitting the table into two.  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.  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!

Hi @Victoria-9 ,

 

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

image.png

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.

Hi @Sergei Baklan

 

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? 

Highlighted

Hi @Victoria-9 ,

 

In Power Query editor you may convert column [Number] to type text on one of the first steps, and on one of the last one convert it back to Whole Number, please see attached. Columns combiner works with texts only.

@Victoria-9 , in addition I added months sorting by adding month number in front of each its name before pivoting; removed that index on latest steps.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies