CODEWITHSUNDEEP
datepowerbipowerbi-desktop
Dade
Dade

Reputation: 33

PowerBI-How to use a date table in a dataset with date columns or change a shape map data selecting another columns in the same table

I have added a CSV file in PowerBi with this structure:

enter image description here

In each mau_audience column I have values for different dates for each country in the table.

In the report I have a shape map like this:

enter image description here

I'm trying to change the data on this map inside the report, selecting each mau_audience column, I am a beginner in PowerBi, and I have already tried to create a date table for this table without success.

I expect to be able to select a different mau_audience column to visualize the different values in the map.

Any help or guidance will be very appreciated.

Upvotes: 0

Views: 220

Answers (1)

Olly
Olly

Reputation: 7891

Your source data has no date value. Each column is considered separately.

You can edit your query to unpivot your source data, and calculate a date from the header names:

let
    Source = Csv.Document(File.Contents("C:\temp\south_america_data.csv"),[Delimiter=",", Columns=10]),
    ReportYear = "2018",
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"admin"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Field", "Date"}),
    #"Split Column by Position" = Table.SplitColumn(#"Split Column by Delimiter", "Date", Splitter.SplitTextByPositions({0, 3}, false), {"Month Name", "Day"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Split Column by Position", "Date", each Date.FromText(Text.Combine({[Day], [Month Name], ReportYear}, " ")), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Month Name", "Day"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Field]), "Field", "Value", List.Sum)
in
    #"Pivoted Column"

Now you can create a relationship to your Date table, and build a measure based on the single mau_audience column.

Upvotes: 1

Related Questions