Changing rows and columns and "grouping" them

[iwhite01]

I am trying to make a visual that will show specific dates and reporting metrics in one table/matrix but I cannot get it to work through any PowerBI features.

My data is connected to a Microsoft list. The data ends up looking like this (extremely simplified):

Site	Date 1	Date 2	Date 3	Target 1	Target 2	Target 3	Metric 1	Metric 2	Metric 3
A	Date 1A	Date 2A	Date 3A	Target 1A	Target 2A	Target 3A	Metric 1A	Metric 2A	Metric 3A
B	Date 1B	Date 2B	Date 3B	Target 1B	Target 2B	Target 3B	Metric 1B	Metric 2B	Metric 3B
C	Date 1C	Date 2C	Date 3C	Target 1C	Target 2C	Target 3C	Metric 1C	Metric 2C	Metric 3C

The date and the target columns are dates and the metric column is a customn column calculation that is a whole number (days between two dates). 

I would like to have the data display in a visual like this:

	Dates	Targets	Metric
Site A	Date 1A	Target 1A	Metric 1A
	Date 2A	Target 2A	Metric 2A
	Date 3A	Target 3A	Metric 3A

Where you select the site and the associated dates, targets, and metrics are broken down into columns.

The closest I can get is by piecing together multiple matrix's and blanking out left column.

Please let me know if there is anything I can do to make this work. 

[v-venuppu]

Hi @iwhite01 ,

May I ask if you have resolved this issue with the solutions provided..?Let us know if you have any further issues.

Thank you.

[v-venuppu]

Hi @iwhite01 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Mathur @lbendlin for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..? If any of the responses has addressed your query, please accept their post as a solution, so other members can easily find it.

Thank you.

[Ashish_Mathur]

Hi,

In the first table that you have shared, please fill up entries under the columns for us to know the data type under each column.

[iwhite01]

The date column is dates, the target column is a date custom column that adds days to the initial date column, and the metric column is custom column that is a whole number representing the number of days between the two dates.

[Ashish_Mathur]

Hi,

Use this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Site"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Site", type text}, {"Date ", type date}, {"Target ", type date}, {"Metric ", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

[lbendlin · Accepted solution]

Unpivot your data and then re-pivot part of it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc07CsAgFETRrQRrm4wriKZNl04sJEhIK+6f+H1Md2AGrvfqUFqdsaRtF0Fkmu6Y31TGPg1y/1yp5O8Zn2mQ6ydor6y0RBAZSy0yyP2zWmSQ66e1nLREEBlHLTLI/bNaZJDrJ4Qf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Site = _t, #"Date 1" = _t, #"Date 2" = _t, #"Date 3" = _t, #"Target 1" = _t, #"Target 2" = _t, #"Target 3" = _t, #"Metric 1" = _t, #"Metric 2" = _t, #"Metric 3" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Site"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute", "Row"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[iwhite01]

Thank you. This got me close enough to figure it out. The issue that developed was that deliminator being a space and then ordering numbers. I also had spaces in some of my value fields. Both of these things did not exist in the actual data and were just given to be examples. I renamed my header columns to be date,1 target,1, etc. This seemed to work.